package com.bj58.data.emailreports.service.impl;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.bj58.data.emailreports.dao.SrcDbPool.DBType;
import com.bj58.data.emailreports.model.BWRAllLineInc;
import com.bj58.data.emailreports.model.BWRAllOne;
import com.bj58.data.emailreports.model.BWRAllOneJL;
import com.bj58.data.emailreports.model.BWRJzFluxFunnel;
import com.bj58.data.emailreports.model.BWRLineAccount;
import com.bj58.data.emailreports.model.BWRLineCityCate;
import com.bj58.data.emailreports.model.BWRLineCusTran;
import com.bj58.data.emailreports.model.BWRLineFlux;
import com.bj58.data.emailreports.model.BWRLineJTChange;
import com.bj58.data.emailreports.model.BWRLineSPIncome;
import com.bj58.data.emailreports.model.BWRNoteEffectData;
import com.bj58.data.emailreports.model.BWRBalanceCusLevel;
import com.bj58.data.emailreports.model.WRConsumCycleVo;
import com.bj58.data.emailreports.model.WRCusFunnelVo;
import com.bj58.data.emailreports.service.BusiWeekReportService;
import com.bj58.data.emailreports.utils.CommonDataDeal;
import com.bj58.data.emailreports.utils.DateUtil;
import com.bj58.data.emailreports.utils.MyStore;
import com.bj58.data.emailreports.utils.ReflectUtil;
import com.bj58.data.emailreports.utils.SortUtil;
import com.bj58.data.emailreports.utils.VelocityTools;

public class BusiWeekReportServiceImpl implements BusiWeekReportService {
	

	private CommonDataDeal comDeal = new CommonDataDeal();
	private ReflectUtil reflectUtil = new ReflectUtil();
	private VelocityTools vt = new VelocityTools();
	private SortUtil sortUtil = new SortUtil();
	
	/**各业务线查询条件Map
	 * @return
	 */
	private static HashMap<String,String>  getBusiLineConsMap()throws Exception{
		HashMap<String,String> busiLineMap = new HashMap<String,String>();
		busiLineMap.put("all", " 1=1 ");
		busiLineMap.put("huangye", MyStore.hyCondition);
		busiLineMap.put("zhaopin", MyStore.zpCondtion);
		
		busiLineMap.put("ershouchuangxin", MyStore.escxCondtion);
		busiLineMap.put("zhaoshangjiameng", MyStore.zsjmCondtion);

		busiLineMap.put("ershouche", MyStore.escCondtion);
		busiLineMap.put("fangchan", MyStore.fcCondtion);
		
		return busiLineMap;
	}
	
	/**检查是否跨月
	 * @param endDate
	 * @return
	 */
	private List<String>  checkTwoMonth(String endDate){
		String beginDate = DateUtil.getStringDate(DateUtil.getDateBefore(DateUtil.strToDate(endDate, "yyyy-MM-dd"), 6), "yyyy-MM-dd") ;
		List<String> monthList =  DateUtil.subSearchDate(beginDate, endDate);
		return monthList;
	}
	
	@Override
	public List<BWRAllOne> getBWRAllOneList(String searchDate)
			throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		String yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
		
		//各业务线 完成数
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT stat_date,business_type,");
		sql.append(" sum(if(income_type in ('jingzhun','zhiding'),cash,0))/1000000 'all', ");
		sql.append(" sum(if(income_type='jingzhun',cash,0))/1000000 'jz', ");
		sql.append(" sum(if(income_type='zhiding',cash,0))/1000000 'zd', ");
		sql.append(" sum(if(income_type='zhineng',cash,0))/1000000 'zn' ");
		sql.append(" FROM t_amortize_day_" + yyyyMM );
		sql.append(" where stat_date ='"+searchDate+"' ");
		sql.append(" and business_type in('huangye','zhaopin','ershou','ershouche','fangchan') ");
		sql.append(" GROUP BY business_type ");
		
		sql.append(" :"+ DBType.db_94_data_app.getValue());
		List<String> cols1 = new ArrayList<String>();
		cols1.add("statDate");
		cols1.add("busiLine");
		cols1.add("allDone");
		cols1.add("jzDone");
		cols1.add("zdDone");
		cols1.add("znDone");
		sqlMap.put(sql.toString(), cols1);
		
		
		//总计 完成数
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT stat_date,'all',");
		sql.append(" sum(if(income_type in ('jingzhun','zhiding'),cash,0))/1000000 'all', ");
		sql.append(" sum(if(income_type='jingzhun',cash,0))/1000000 'jz', ");
		sql.append(" sum(if(income_type='zhiding',cash,0))/1000000 'zd', ");
		sql.append(" sum(if(income_type='zhineng',cash,0))/1000000 'zn' ");
		sql.append(" FROM t_amortize_day_" + yyyyMM );
		sql.append(" where stat_date ='"+searchDate+"' ");
		
		sql.append(" :"+ DBType.db_94_data_app.getValue());
		List<String> cols2 = new ArrayList<String>();
		cols2.add("statDate");
		cols2.add("busiLine");
		cols2.add("allDone");
		cols2.add("jzDone");
		cols2.add("zdDone");
		cols2.add("znDone");
		sqlMap.put(sql.toString(), cols2);
		
		//KPI
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT '"+searchDate+"',busiLine, ");
		sql.append(" allKpi,jzKpi,zdKpi,znKpi ");
		sql.append(" FROM kpi_week_report ");
		sql.append(" where month ='" + yyyyMM + "' ");
		sql.append(" group by busiLine ");
		
		sql.append(" :"+ DBType.db_68_bi_sys.getValue());
		List<String> cols3 = new ArrayList<String>();
		cols3.add("statDate");
		cols3.add("busiLine");
		cols3.add("allKpi");
		cols3.add("jzKpi");
		cols3.add("zdKpi");
		cols3.add("znKpi");
		sqlMap.put(sql.toString(), cols3);
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		
		List<BWRAllOne> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRAllOne.class, gbList);
		
		//计算完成率
		for (BWRAllOne vo : rsList) {
			String busiLine = vo.getBusiLine();
			vo.setBusiLine(MyStore.busilineTran(busiLine));		
			vo.setAllHasDone(vo.getAllDone()/vo.getAllKpi());		
			vo.setJzHasDone(vo.getJzDone()/vo.getJzKpi());	
			vo.setZdHasDone(vo.getZdDone()/vo.getZdKpi());	
			if(vo.getZnKpi() > 0 ){
				vo.setZnHasDone(vo.getZnDone()/vo.getZnKpi());
			}
		}
		return rsList;
	}

	@Override
	public Map<String,BWRAllOneJL> getBWRAllOneJLList(List<String> dateList)
			throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		String today = dateList.get(0);
		String yyyyMM = today.substring(0,today.lastIndexOf("-")).replace("-", "");
			
		//简历摊销本月完成数和日均摊销
		sql.delete(0, sql.length()+1);
		
		sql.append(" SELECT '" + today + "', ");
		sql.append(" sum(a.tx) as '完成摊销',sum(a.tx)/count(distinct vDate) as '日均摊销' ");
		sql.append(" from( ");
		sql.append(" select vDate,sum(dayAmortize) tx ");
		sql.append(" from amortize_metrics ");
		sql.append(" where memberId in(1038,1097,1104)   ");
		sql.append(" and vDate >= '" +  today.substring(0,today.lastIndexOf("-") + 1)  +"01' ");
		sql.append(" and vDate <= '"+today+ "' ");
		sql.append(" group by vDate)a; ");
		
		sql.append(" :"+ DBType.db_131_ors.getValue());
		List<String> cols1 = new ArrayList<String>();
		cols1.add("statDate");
		cols1.add("doneAmor");
		cols1.add("dayAvgAmor");
		sqlMap.put(sql.toString(), cols1);	
			
		//KPI	
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT '" + today + "',kpi ");
		sql.append(" FROM jianli_report_kpi " );
		sql.append(" where month ='" + today.substring(0,today.lastIndexOf("-"))+"'" );
	
		sql.append(" :"+ DBType.db_68_bi_sys.getValue());
		List<String> cols2 = new ArrayList<String>();
		cols2.add("statDate");
		cols2.add("kpi");
		sqlMap.put(sql.toString(), cols2);
			
		//简历购买收入、客户数
		for(String searchDate : dateList){
			
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);			
			
			sql.delete(0, sql.length()+1);
			sql.append(" select '"+searchDate+"',");
			sql.append(" sum(a.gm)/count(distinct stat_date) as '购买收入',");
			sql.append(" sum(a.kh)/count(distinct stat_date) as '购买客户数',");
			sql.append(" sum(a.gm)/sum(a.kh) as 'ARPU', ");
			sql.append(" round(sum(jlxz_vip)/count(DISTINCT stat_date),1) AS'简历下载收入-vip',");
			sql.append(" round(sum(jlxz_kh_vip)/count(DISTINCT stat_date),1)AS '购买客户数-vip', ");
			sql.append(" round(sum(jlxz_vip)/sum(jlxz_kh_vip),2) AS 'ARPU-vip' ");
			
			sql.append(" from( ");			
			sql.append(" select stat_date,sum(resume_amount-discount_amount) gm,count(distinct user_id) kh, ");
			sql.append("  SUM(case when user_type=3 then (resume_amount-discount_amount) end) as 'jlxz_vip', ");	
			sql.append("  COUNT(distinct case when user_type=3 then user_id end) as 'jlxz_kh_vip' ");	
			
			sql.append(" from udp_buyResumePackage_detail_" +  monthList.get(0).split(";")[0]);
			sql.append(" where  stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
			sql.append(" and stat_date <= '"+searchDate+"'  ");
			sql.append(" and (resume_amount-discount_amount)>0 ");
			sql.append(" group by stat_date ");		
			if(monthList.size() > 1){
				sql.append(" UNION all  ");
				sql.append(" select stat_date,sum(resume_amount-discount_amount) gm,count(distinct user_id) kh, ");
				sql.append("  SUM(case when user_type=3 then (resume_amount-discount_amount) end) as 'jlxz_vip', ");	
				sql.append("  COUNT(distinct case when user_type=3 then user_id end) as 'jlxz_kh_vip' ");	
				sql.append(" from udp_buyResumePackage_detail_" + monthList.get(1).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");
				sql.append(" and (resume_amount-discount_amount)>0 ");
				sql.append(" group by stat_date ");
			}
			sql.append(" )a;");
			
			sql.append(" :"+ DBType.db_94_data_app.getValue());
			List<String> cols3 = new ArrayList<String>();
			cols3.add("statDate");
			cols3.add("income");
			cols3.add("cusCount");
			cols3.add("ARPU");
			cols3.add("income_vip");
			cols3.add("cusCount_vip");
			cols3.add("ARPU_vip");
			sqlMap.put(sql.toString(), cols3);
						
			//本周日均摊销
			sql.delete(0, sql.length()+1);
			
			sql.append(" SELECT '" + searchDate + "', ");
			sql.append(" sum(a.tx)/count(distinct vDate) as '日均摊销' ");
			sql.append(" from( ");
			sql.append(" select vDate,sum(dayAmortize) tx ");
			sql.append(" from amortize_metrics ");
			sql.append(" where memberId in(1038,1097,1104)   ");
			sql.append(" and vDate >= date_sub('"+searchDate+"',interval 6 day)  ");
			sql.append(" and vDate <= '"+searchDate+ "' ");
			
			sql.append(" group by vDate)a; ");
			sql.append(" :"+ DBType.db_131_ors.getValue());
			List<String> cols4 = new ArrayList<String>();
			cols4.add("statDate");
			cols4.add("dayAvgAmorWeek");
			
			sqlMap.put(sql.toString(), cols4);
		}

		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		
		List<BWRAllOneJL> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRAllOneJL.class, gbList);
		
		Map<String,BWRAllOneJL> rsMap = new HashMap<String, BWRAllOneJL>();
		//最后计算  日均摊销  实际进度
		for (BWRAllOneJL vo : rsList) {
			if(vo.getStatDate().equals(today)){
				if(vo.getKpi()==0)
				{
					vo.setDone(0);
				}else{
					vo.setDone(vo.getDoneAmor()/vo.getKpi());
				}
				
			}		
			rsMap.put(vo.getStatDate(), vo);
		}	
		return rsMap;
	}


	@Override
	public List<BWRAllLineInc> getBWRAllLineIncList(List<String> dateList,String condition)
			throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		List<String> incomeTypeList = new ArrayList<String>();
		incomeTypeList.add("jingzhun");
		incomeTypeList.add("zhiding");
		incomeTypeList.add("zhineng");
		
		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);			
			for(String incomeType : incomeTypeList){
				
				for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
					String busiName = it.next();
					
					sql.delete(0, sql.length()+1);					
					sql.append(" select '" + searchDate + "','"+busiName+"',");
					sql.append(" round(sum(a.xf)/ count(distinct a.stat_date),2)  as'日均消费', ");
					sql.append(" round(sum(a.xjxf)/ count(distinct a.stat_date),2)  as'现金消费',");
					sql.append(" round(sum(a.kh)/ count(distinct a.stat_date),2)  as'客户数', ");
					sql.append(" (round(sum(a.xf)/ count(distinct a.stat_date),2))/(round(sum(a.kh)/ count(distinct a.stat_date),2))  as'ARPU' ");
					sql.append(" from( ");
					sql.append(" select stat_date,sum(cost)/100 xf,sum(cash)/100 xjxf ,count(distinct if(cost>0,user_id,null)) kh ");
					
					if("zhiding".equals(incomeType)){
						sql.append(" from t_"+incomeType+"_user_stat_belong_"  + monthList.get(0).split(";")[0]);
					}
					else{
						sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
					}
									
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					
					if("vip".equals(condition))
					{
						sql.append(" and is_vip=1 ");
					}
					
					if(!"all".equals(busiName)){
						if("ershouchuangxin".equals(busiName)){
							if("zhiding".equals(incomeType)){
								sql.append(" and " + MyStore.getBelongCateCons(busiName) );
							}
							else  sql.append(" and " + busiLineConsMap.get(busiName) );
						}else if("zhaoshangjiameng".equals(busiName)){
							if("zhiding".equals(incomeType)){
								sql.append(" and " + MyStore.getBelongCateCons(busiName) );
							}
							else  sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
						}
					}
					sql.append(" group by stat_date ");
					//跨月处理
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select stat_date,sum(cost)/100 xf,sum(cash)/100 xjxf ,count(distinct if(cost>0,user_id,null)) kh ");
			
						if("zhiding".equals(incomeType)){
							sql.append(" from t_"+incomeType+"_user_stat_belong_"  + monthList.get(1).split(";")[0]);
						}
						else{
							sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
						}
						
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");
						
						if("vip".equals(condition))
						{
							sql.append(" and is_vip=1 ");
						}
						
						if(!"all".equals(busiName)){
							if("ershouchuangxin".equals(busiName)){
								if("zhiding".equals(incomeType)){
									sql.append(" and " + MyStore.getBelongCateCons(busiName) );
								}
								else  sql.append(" and " + MyStore.escxCondtion );
							}else if("zhaoshangjiameng".equals(busiName)){
								if("zhiding".equals(incomeType)){
									sql.append(" and " + MyStore.getBelongCateCons(busiName) );
								}
								else  sql.append(" and " + MyStore.zsjmCondtion );
							}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
							}
						}
						sql.append(" group by stat_date ");
					}
					sql.append(" )a ");
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					
					
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("busiLine");
					
					if("jingzhun".equals(incomeType)){
						cols1.add("jzCost");
						cols1.add("jzCash");
						cols1.add("jzCusCount");
						cols1.add("jzARPU");
					}else if("zhiding".equals(incomeType)){
						cols1.add("zdCost");
						cols1.add("zdCash");
						cols1.add("zdCusCount");
						cols1.add("zdARPU");
					}else if("zhineng".equals(incomeType)){
						cols1.add("znCost");
						cols1.add("znCash");
						cols1.add("znCusCount");
						cols1.add("znARPU");
					}
					
					sqlMap.put(sql.toString(), cols1);
				}
			}
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		
		List<BWRAllLineInc> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRAllLineInc.class, gbList);
		
		
		//计算 各业务线的总 cost/cash/cusCount/ARPU
		for (BWRAllLineInc vo : rsList) {
			vo.setCost(vo.getJzCost() + vo.getZdCost() + vo.getZnCost());
			vo.setCash(vo.getJzCash() + vo.getZdCash() + vo.getZnCash());
			vo.setCusCount(vo.getJzCusCount() + vo.getZdCusCount() + vo.getZnCusCount());
			if(vo.getCusCount()==0)
			{
				vo.setARPU(0);
			}
			else 
				vo.setARPU((vo.getCost()) / vo.getCusCount());
			
			vo.setBusiLine(MyStore.busilineTran(vo.getBusiLine()));
		}
		return rsList;
	}


	@Override
	public Map<String,List<BWRLineFlux>> getBWRAllLineFluxList(List<String> dateList)
			throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		List<String> incomeTypeList = new ArrayList<String>();
		incomeTypeList.add("jingzhun");
		incomeTypeList.add("zhiding");
		incomeTypeList.add("zhineng");
		
		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);
			
			for(String incomeType : incomeTypeList){
				
				for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
					String busiName = it.next();

					sql.delete(0, sql.length()+1);
					
					sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType + "',");
					sql.append(" round(sum(a.pv)/ count(distinct a.stat_date),2)  as'PV(W)', ");
					sql.append(" round(sum(a.adpv)/ count(distinct a.stat_date),2)  as'ADPV(W)',");
					sql.append(" round(sum(a.disp)/ count(distinct a.stat_date),2)  as'ADIMP(W)',");
					sql.append(" round(sum(a.click)/ count(distinct a.stat_date),2)  as'CLICK',");
					sql.append(" (round(sum(a.adpv)/ count(distinct a.stat_date),2)) /(round(sum(a.pv)/ count(distinct a.stat_date),2) ) as 'PVR', ");//PVR = ADPV/PV
					sql.append(" (round(sum(a.disp)/ count(distinct a.stat_date),2)) /(round(sum(a.adpv)/ count(distinct a.stat_date),2) ) as 'ASN', ");//ASN = ADIMP/ADPV
					sql.append(" (round(sum(a.cost)/ count(distinct a.stat_date),2)*1000) /(round(sum(a.disp)/ count(distinct a.stat_date),2) *10000) as 'CPM', ");//CPM = COST/ADIMP*1000
					sql.append(" (round(sum(a.cost)/ count(distinct a.stat_date),2)) /(round(sum(a.click)/ count(distinct a.stat_date),2) ) as 'ACP', ");//ACP=消费/CLICK
					sql.append(" (round(sum(a.click)/ count(distinct a.stat_date),2)) /(round(sum(a.disp)/ count(distinct a.stat_date),2) * 10000 ) as 'CTR2' ");//CTR2=CLICK/ADIMP
					
					sql.append(" from( ");
					sql.append(" select stat_date,sum(pv)/10000 pv,sum(adpv)/10000 adpv , ");
					sql.append(" sum(disp)/10000 disp,sum(click) click,sum(cost/100) cost  ");
					sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + monthList.get(0).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					if(!"all".equals(busiName)){
						if("ershouchuangxin".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
						}
					}
					sql.append(" group by stat_date ");
					//跨月处理
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select stat_date,sum(pv)/10000 pv,sum(adpv)/10000 adpv , ");
						sql.append(" sum(disp)/10000 disp,sum(click) click,sum(cost/100) cost  ");
						sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + monthList.get(1).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");
						if(!"all".equals(busiName)){
							if("ershouchuangxin".equals(busiName)){							
								sql.append(" and " + busiLineConsMap.get(busiName) );
							}else if("zhaoshangjiameng".equals(busiName)){							
								sql.append(" and " + busiLineConsMap.get(busiName) );
							}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
							}
						}
						sql.append(" group by stat_date ");
					}
					sql.append(" )a ");
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("busiLine");
					cols1.add("incomeType");
					
					cols1.add("pv");
					cols1.add("adpv");
					cols1.add("adimp");
					cols1.add("click");
					cols1.add("PVR");
					cols1.add("ASN");
					cols1.add("CPM");
					cols1.add("ACP");
					cols1.add("CTR2");
					
					sqlMap.put(sql.toString(), cols1);
				}
			}
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("incomeType");
		
		List<BWRLineFlux> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineFlux.class, gbList);
		
		Map<String,List<BWRLineFlux>>  rsMap = new HashMap<String, List<BWRLineFlux>>();
		for (BWRLineFlux vo : rsList) {
			vo.setIncomeType(MyStore.incomeTypeTran(vo.getIncomeType()));
			
			String busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			List<BWRLineFlux> tempList = new ArrayList<BWRLineFlux>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
			
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}
		
		return rsMap;
	}

	@Override
	public Map<String,List<BWRLineFlux>> getBWRLinePageFluxList(List<String> dateList)
			throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		List<String> incomeTypeList = new ArrayList<String>();
		incomeTypeList.add("jingzhun");
		incomeTypeList.add("jingzhun-list");
		incomeTypeList.add("jingzhun-detail");
		incomeTypeList.add("zhiding");
		incomeTypeList.add("zhiding-list");
		incomeTypeList.add("zhiding-detail");		
		incomeTypeList.add("zhineng");
		
		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);			
			for(String incomeType : incomeTypeList){
				
				for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
					String busiName = it.next();
					
					if("all".equals(busiName)){ //总体 PASS
						continue;
					}
					
					sql.delete(0, sql.length()+1);					
					sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType + "',");
					sql.append(" round(sum(a.pv)/ count(distinct a.stat_date),5)  as'PV(W)', ");
					sql.append(" round(sum(a.adpv)/ count(distinct a.stat_date),5)  as'ADPV(W)',");
					sql.append(" round(sum(a.disp)/ count(distinct a.stat_date),5)  as'ADIMP(W)',");
					sql.append(" round(sum(a.click)/ count(distinct a.stat_date),5)  as'CLICK',");
					sql.append(" (round(sum(a.adpv)/ count(distinct a.stat_date),2)) /(round(sum(a.pv)/ count(distinct a.stat_date),2) ) as 'PVR', ");//PVR = ADPV/PV
					sql.append(" (round(sum(a.disp)/ count(distinct a.stat_date),2)) /(round(sum(a.adpv)/ count(distinct a.stat_date),2) ) as 'ASN', ");//ASN = ADIMP/ADPV
					sql.append(" (round(sum(a.cost)/ count(distinct a.stat_date),2)*1000) /(round(sum(a.disp)/ count(distinct a.stat_date),2) *10000) as 'CPM', ");//CPM = COST/ADIMP*1000
					sql.append(" (round(sum(a.cost)/ count(distinct a.stat_date),2)) /(round(sum(a.click)/ count(distinct a.stat_date),2) ) as 'ACP', ");//ACP=消费/CLICK
					sql.append(" (round(sum(a.click)/ count(distinct a.stat_date),2)) /(round(sum(a.disp)/ count(distinct a.stat_date),2) * 10000 ) as 'CTR2' ");//CTR2=CLICK/ADIMP
					
					sql.append(" from( ");
					sql.append(" select stat_date,sum(pv)/10000 pv,sum(adpv)/10000 adpv , ");
					sql.append(" sum(disp)/10000 disp,sum(click) click,sum(cost/100) cost  ");
					if(incomeType.contains("jingzhun"))
					{
						sql.append(" from t_jingzhun_source_cate_pagetype_" + monthList.get(0).split(";")[0]);
					}else if(incomeType.contains("zhiding"))
					{
						sql.append(" from t_zhiding_source_cate_pagetype_" + monthList.get(0).split(";")[0]);
					}else
					{
						sql.append(" from t_zhineng_city_cate_stat_new_" + monthList.get(0).split(";")[0]);
					}
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					
					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					if(incomeType.contains("list"))
					{
						sql.append(" and page_type=1");
					}else if(incomeType.contains("detail"))
					{
						sql.append(" and page_type=2");
					}
					sql.append(" group by stat_date ");
					//跨月处理
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select stat_date,sum(pv)/10000 pv,sum(adpv)/10000 adpv , ");
						sql.append(" sum(disp)/10000 disp,sum(click) click,sum(cost/100) cost  ");
						if(incomeType.contains("jingzhun"))
						{
							sql.append(" from t_jingzhun_source_cate_pagetype_" + monthList.get(1).split(";")[0]);
						}else if(incomeType.contains("zhiding"))
						{
							sql.append(" from t_zhiding_source_cate_pagetype_" + monthList.get(1).split(";")[0]);
						}else
						{
							sql.append(" from t_zhineng_city_cate_stat_new_" + monthList.get(1).split(";")[0]);
						}
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");
						
						if("ershouchuangxin".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						if(incomeType.contains("list"))
						{
							sql.append(" and page_type=1");
						}else if(incomeType.contains("detail"))
						{
							sql.append(" and page_type=2");
						}
						sql.append(" group by stat_date ");
					}
					sql.append(" )a ");
					
					sql.append(" :"+ DBType.db_131_data_bi.getValue());
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("busiLine");
					cols1.add("incomeType");
					
					cols1.add("pv");
					cols1.add("adpv");
					cols1.add("adimp");
					cols1.add("click");
					cols1.add("PVR");
					cols1.add("ASN");
					cols1.add("CPM");
					cols1.add("ACP");
					cols1.add("CTR2");
					
					sqlMap.put(sql.toString(), cols1);
				}
			}
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("incomeType");
		
		List<BWRLineFlux> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineFlux.class, gbList);
		
		Map<String,List<BWRLineFlux>>  rsMap = new HashMap<String, List<BWRLineFlux>>();
		for (BWRLineFlux vo : rsList) {
			vo.setIncomeType(MyStore.incomeTypeTran2(vo.getIncomeType()));
			
			String busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			List<BWRLineFlux> tempList = new ArrayList<BWRLineFlux>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
			
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}
		
		return rsMap;
	}

	@Override
	public Map<String,List<BWRLineFlux>> getBWRLineJingzhunFluxList(List<String> dateList)
			throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		List<String> platformTypeList = new ArrayList<String>();
		platformTypeList.add("PC");
		platformTypeList.add("M");
		platformTypeList.add("APP");
		
		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);			
			for(String platformType : platformTypeList){
				
				for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
					String busiName = it.next();
					
					if("all".equals(busiName)){ //总体 PASS
						continue;
					}
		
					sql.delete(0, sql.length()+1);				
					sql.append(" select '" + searchDate + "','"+busiName+"','" + platformType + "',");
					sql.append(" round(sum(a.pv)/ count(distinct a.stat_date),5)  as'PV(W)', ");
					sql.append(" round(sum(a.adpv)/ count(distinct a.stat_date),5)  as'ADPV(W)',");
					sql.append(" round(sum(a.disp)/ count(distinct a.stat_date),5)  as'ADIMP(W)',");
					sql.append(" round(sum(a.click)/ count(distinct a.stat_date),5)  as'CLICK',");
					sql.append(" (round(sum(a.adpv)/ count(distinct a.stat_date),2)) /(round(sum(a.pv)/ count(distinct a.stat_date),2) ) as 'PVR', ");//PVR = ADPV/PV
					sql.append(" (round(sum(a.disp)/ count(distinct a.stat_date),2)) /(round(sum(a.adpv)/ count(distinct a.stat_date),2) ) as 'ASN', ");//ASN = ADIMP/ADPV
					sql.append(" (round(sum(a.cost)/ count(distinct a.stat_date),2)*1000) /(round(sum(a.disp)/ count(distinct a.stat_date),2) *10000) as 'CPM', ");//CPM = COST/ADIMP*1000
					sql.append(" (round(sum(a.cost)/ count(distinct a.stat_date),2)) /(round(sum(a.click)/ count(distinct a.stat_date),2) ) as 'ACP', ");//ACP=消费/CLICK
					sql.append(" (round(sum(a.click)/ count(distinct a.stat_date),2)) /(round(sum(a.disp)/ count(distinct a.stat_date),2) * 10000 ) as 'CTR2' ");//CTR2=CLICK/ADIMP
					
					sql.append(" from( ");
					sql.append(" select stat_date,sum(pv)/10000 pv,sum(adpv)/10000 adpv , ");
					sql.append(" sum(disp)/10000 disp,sum(click) click,sum(cost/100) cost  ");
					sql.append(" from t_jingzhun_source_cate_pagetype_" + monthList.get(0).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");

					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					if(platformType.equals("PC"))
					{
						sql.append(" and platform=-1");
						
					}else if(platformType.equals("M"))
					{
						sql.append(" and platform=0");
					}else
					{
						sql.append(" and platform=2");
					}
					sql.append(" group by stat_date ");
					//跨月处理
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select stat_date,sum(pv)/10000 pv,sum(adpv)/10000 adpv , ");
						sql.append(" sum(disp)/10000 disp,sum(click) click,sum(cost/100) cost  ");
						sql.append(" from t_jingzhun_source_cate_pagetype_" + monthList.get(1).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");

						if("ershouchuangxin".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						if(platformType.equals("PC"))
						{
							sql.append(" and platform=-1");
							
						}else if(platformType.equals("M"))
						{
							sql.append(" and platform=0");
						}else
						{
							sql.append(" and platform=2");
						}
						sql.append(" group by stat_date ");
					}
					sql.append(" )a ");
					
					sql.append(" :"+ DBType.db_131_data_bi.getValue());
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("busiLine");
					cols1.add("incomeType");
					
					cols1.add("pv");
					cols1.add("adpv");
					cols1.add("adimp");
					cols1.add("click");
					cols1.add("PVR");
					cols1.add("ASN");
					cols1.add("CPM");
					cols1.add("ACP");
					cols1.add("CTR2");
					
					sqlMap.put(sql.toString(), cols1);
				}
			}
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("incomeType");
		
		List<BWRLineFlux> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineFlux.class, gbList);
		
		Map<String,List<BWRLineFlux>>  rsMap = new HashMap<String, List<BWRLineFlux>>();
		for (BWRLineFlux vo : rsList) {
			String busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			List<BWRLineFlux> tempList = new ArrayList<BWRLineFlux>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}	
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}		
		return rsMap;
	}

	@Override
	public Map<String, List<BWRLineJTChange>> getBWRLineJTChangeMap(
			List<String> dateList) throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		
		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);
			String yyyy=searchDate.substring(0, 4);
			
			for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
				String busiName = it.next();
				String conditions = busiLineConsMap.get(busiName);
				
				if("all".equals(busiName)){ //总体不看
					continue;
				}				
				
				/**精准指标*/
				//精准总预算
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" sum(caculate_budget)/count(distinct stat_date) as  '总（计算）预算'  ");
				sql.append(" from (");
				sql.append(" select stat_date,sum(caculate_budget/100) as caculate_budget ");
				sql.append(" from t_jingzhun_cpc_user_yusuan_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");
				sql.append(" and " + conditions.replace("disp_cate1_name", "source_cate1_name").replace("disp_cate2_name", "source_cate2_name")  );
				sql.append(" group by stat_date ");
				if(monthList.size() > 1){
					sql.append(" UNION all  ");
					sql.append(" select stat_date,sum(caculate_budget/100) as caculate_budget ");
					sql.append(" from t_jingzhun_cpc_user_yusuan_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					sql.append(" and " + conditions.replace("disp_cate1_name", "source_cate1_name").replace("disp_cate2_name", "source_cate2_name")  );
					sql.append(" group by stat_date ");
				}
				sql.append(" ) a");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("busiLine");
				cols1.add("jzAllBudget");
				
				sqlMap.put(sql.toString(), cols1);
				
				//精准设定价格
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" sum(a.sdjg)/count(distinct stat_date) as '平均设定价格'  ");
				sql.append(" from (");
				sql.append(" select stat_date,sum(bid)/count(distinct subscribe_id)/100 as sdjg  ");
				sql.append(" from t_jingzhun_cpc_subscribe_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");
				sql.append(" and open_state=0  and  creative_state in(0,5,6) ");

				if("ershouchuangxin".equals(busiName)){							
					sql.append(" and " + MyStore.escxCondtion.replace("disp_cate1_name", "source_cate1_name").replace("disp_cate2_name", "source_cate2_name") );
				}else if("zhaoshangjiameng".equals(busiName)){							
					sql.append(" and " + MyStore.zsjmCondtion.replace("disp_cate1_name", "source_cate1_name").replace("disp_cate2_name", "source_cate2_name") );
				}else{
						sql.append(" and business_type_biz ='" + busiName +"' ");
				}

				sql.append(" group by stat_date ");
				if(monthList.size() > 1){
					sql.append(" UNION all  ");
					sql.append(" select stat_date,sum(bid)/count(distinct subscribe_id)/100 as sdjg  ");
					sql.append(" from t_jingzhun_cpc_subscribe_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					sql.append(" and open_state=0  and  creative_state in(0,5,6) ");

					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + MyStore.escxCondtion.replace("disp_cate1_name", "source_cate1_name").replace("disp_cate2_name", "source_cate2_name") );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + MyStore.zsjmCondtion.replace("disp_cate1_name", "source_cate1_name").replace("disp_cate2_name", "source_cate2_name") );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					sql.append(" group by stat_date ");
				}
				sql.append(" ) a");
				
				sql.append(" :"+ DBType.db_131_data_bi.getValue());
				List<String> cols2 = new ArrayList<String>();
				cols2.add("statDate");
				cols2.add("busiLine");
				cols2.add("jzAvgBid");
				
				sqlMap.put(sql.toString(), cols2);
				
				
				//精准参与客户数  精准展现客户数  精准展现客户比例
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" sum(a.zxkh)/ count(distinct a.stat_date)  as'展现客户数', ");
				sql.append(" sum(a.cykh)/ count(distinct a.stat_date)  as'参与客户数',  ");
				sql.append(" (sum(a.zxkh)/ count(distinct a.stat_date)) /(sum(a.cykh)/ count(distinct a.stat_date))   as '展现客户比例'  ");
				sql.append(" from (");
				sql.append(" select stat_date,count(distinct user_id) cykh,count(distinct if(disp>0,user_id,null)) zxkh   ");
				sql.append(" from  t_jingzhun_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");

				if("ershouchuangxin".equals(busiName)){							
					sql.append(" and " + MyStore.escxCondtion );
				}else if("zhaoshangjiameng".equals(busiName)){							
					sql.append(" and " + MyStore.zsjmCondtion );
				}else{
						sql.append(" and business_type_biz ='" + busiName +"' ");
				}
				
				sql.append(" group by stat_date ");
				if(monthList.size() > 1){
					sql.append(" UNION all  ");
					sql.append(" select stat_date,count(distinct user_id) cykh,count(distinct if(disp>0,user_id,null)) zxkh   ");
					sql.append(" from  t_jingzhun_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");

					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + MyStore.escxCondtion );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + MyStore.zsjmCondtion );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append(" group by stat_date ");
				}
				sql.append(" ) a");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols3 = new ArrayList<String>();
				cols3.add("statDate");
				cols3.add("busiLine");
				cols3.add("jzShowCus");
				cols3.add("jzBidCus");
				cols3.add("jzShowCusRate");
				sqlMap.put(sql.toString(), cols3);
				
				
				/** 置顶指标 */
				
				//置顶小时单价 ，置顶时长
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" sum(a.dj)/sum(a.sc) as '置顶小时单价',  ");
				sql.append(" sum(a.sc)/count(distinct stat_date) as '置顶时长'   ");
				sql.append(" from (");
				sql.append(" select stat_date,sum(consume)/100 as dj,sum(top_hour) as sc   ");
				sql.append(" from t_zhiding_consume_"+yyyy+"");
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");
				sql.append(" and " + MyStore.getBelongCateCons(busiName) );
				sql.append(" group by 1)a; ");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols4 = new ArrayList<String>();
				cols4.add("statDate");
				cols4.add("busiLine");
				cols4.add("zdHourPrice");
				cols4.add("zdTime");
				sqlMap.put(sql.toString(), cols4);
				
				
				//置顶参与客户数 + 置顶展现客户数 + 置顶展现客户比例
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" sum(a.zxkh)/ count(distinct a.stat_date)  as'展现客户数', ");
				sql.append(" sum(a.cykh)/ count(distinct a.stat_date)  as'参与客户数',  ");
				sql.append(" (sum(a.zxkh)/ count(distinct a.stat_date)) /(sum(a.cykh)/ count(distinct a.stat_date))   as '展现客户比例'  ");
				sql.append(" from (");
				sql.append(" select stat_date,count(distinct user_id) cykh,count(distinct if(disp>0,user_id,null)) zxkh   ");
				sql.append(" from  t_zhiding_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");

				if("ershouchuangxin".equals(busiName)){							
					sql.append(" and " + MyStore.escxCondtion );
				}else if("zhaoshangjiameng".equals(busiName)){							
					sql.append(" and " + MyStore.zsjmCondtion );
				}else{
						sql.append(" and business_type_biz ='" + busiName +"' ");
				}
				
				sql.append(" group by stat_date ");
				if(monthList.size() > 1){
					sql.append(" UNION all  ");
					sql.append(" select stat_date,count(distinct user_id) cykh,count(distinct if(disp>0,user_id,null)) zxkh   ");
					sql.append(" from  t_zhiding_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");

					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + MyStore.escxCondtion );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + MyStore.zsjmCondtion );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append(" group by stat_date ");
				}
				sql.append(" ) a");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols5 = new ArrayList<String>();
				cols5.add("statDate");
				cols5.add("busiLine");
				cols5.add("zdShowCus");
				cols5.add("zdBidCus");
				cols5.add("zdShowCusRate");
				sqlMap.put(sql.toString(), cols5);
			}
		
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		
		List<BWRLineJTChange> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineJTChange.class, gbList);
		Map<String,List<BWRLineJTChange>>  rsMap = new HashMap<String, List<BWRLineJTChange>>();
		for (BWRLineJTChange vo : rsList) {
			String busiLine = vo.getBusiLine();
			busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			List<BWRLineJTChange> tempList = new ArrayList<BWRLineJTChange>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
			
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}
		return rsMap;
	}

	@Override
	public Map<String, List<BWRJzFluxFunnel>> getBWRJzFluxFunnelCityMap(
			List<String> dateList) throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		
		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);		
			for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
				String busiName = it.next();
				
				if("all".equals(busiName)){ //总体不看
					continue;
				}
			
				//分城市
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" disp_city1_name, SUM(cost/100)/COUNT(DISTINCT stat_date) as '消费', ");
				sql.append(" sum(pv/10000)/COUNT(DISTINCT stat_date) as '频道pv(W)', ");
				sql.append(" SUM(adpv)/SUM(pv) as 'PVR', ");
				sql.append(" SUM(disp/10000)/COUNT(DISTINCT stat_date) as 'ADIMP(W)', ");
				sql.append(" SUM(disp)/SUM(adpv) as 'ASN', ");
				sql.append(" SUM(click)/COUNT(DISTINCT stat_date) as '点击量', ");
				sql.append(" SUM(click)/SUM(pv) as 'CTR1', ");
				sql.append(" SUM(click)/SUM(disp) as 'CTR2', ");
				sql.append(" SUM(click)/SUM(adpv) as 'CTR3' ");
				sql.append(" from (");
				sql.append(" SELECT stat_date,disp_city1_name ,sum(cost) cost,sum(pv) pv,sum(adpv) adpv,");
				sql.append(" sum(disp) disp,sum(click) click ");
				sql.append(" from t_jingzhun_city_cate_stat_new_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");

				if("ershouchuangxin".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) );
				}else if("zhaoshangjiameng".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) );
				}else{
						sql.append(" and business_type_biz ='" + busiName +"' ");
				}
				
				sql.append(" and disp_city1_name in ('北京', '上海', '广州', '深圳', '天津', '成都')  ");
				sql.append(" GROUP BY stat_date,disp_city1_name ");
				if(monthList.size() > 1){
					sql.append(" UNION all  ");
					sql.append(" SELECT stat_date,disp_city1_name ,sum(cost) cost,sum(pv) pv,sum(adpv) adpv,");
					sql.append(" sum(disp) disp,sum(click) click ");
					sql.append(" from t_jingzhun_city_cate_stat_new_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");

					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append(" and disp_city1_name in ('北京', '上海', '广州', '深圳', '天津', '成都')  ");
					sql.append(" GROUP BY stat_date,disp_city1_name ");
				}
				sql.append(" ) a");
				sql.append(" GROUP BY disp_city1_name ");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("busiLine");
				cols1.add("type");
				cols1.add("cost");
				cols1.add("pv");
				cols1.add("PVR");
				cols1.add("adimp");
				cols1.add("ASN");
				cols1.add("click");
				cols1.add("CTR1");
				cols1.add("CTR2");
				cols1.add("CTR3");
				
				sqlMap.put(sql.toString(), cols1);
				
				//全国
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" 'quanguo', SUM(cost/100)/COUNT(DISTINCT stat_date) as '消费', ");
				sql.append(" sum(pv/10000)/COUNT(DISTINCT stat_date) as '频道pv(W)', ");
				sql.append(" SUM(adpv)/SUM(pv) as 'PVR', ");
				sql.append(" SUM(disp/10000)/COUNT(DISTINCT stat_date) as 'ADIMP(W)', ");
				sql.append(" SUM(disp)/SUM(adpv) as 'ASN', ");
				sql.append(" SUM(click)/COUNT(DISTINCT stat_date) as '点击量', ");
				sql.append(" SUM(click)/SUM(pv) as 'CTR1', ");
				sql.append(" SUM(click)/SUM(disp) as 'CTR2', ");
				sql.append(" SUM(click)/SUM(adpv) as 'CTR3' ");
				sql.append(" from (");
				sql.append(" SELECT stat_date ,sum(cost) cost,sum(pv) pv,sum(adpv) adpv,");
				sql.append(" sum(disp) disp,sum(click) click ");
				sql.append(" from t_jingzhun_city_cate_stat_new_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");

				if("ershouchuangxin".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) );
				}else if("zhaoshangjiameng".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) );
				}else{
						sql.append(" and business_type_biz ='" + busiName +"' ");
				}
				
				sql.append(" GROUP BY stat_date ");
				if(monthList.size() > 1){
					sql.append(" UNION all  ");
					sql.append(" SELECT stat_date ,sum(cost) cost,sum(pv) pv,sum(adpv) adpv,");
					sql.append(" sum(disp) disp,sum(click) click ");
					sql.append(" from t_jingzhun_city_cate_stat_new_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");

					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append(" GROUP BY stat_date ");
				}
				sql.append(" ) a");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				
				sqlMap.put(sql.toString(), cols1);
				
			}
		
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("type");
		
		List<BWRJzFluxFunnel> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRJzFluxFunnel.class, gbList);
		Map<String,List<BWRJzFluxFunnel>>  rsMap = new HashMap<String, List<BWRJzFluxFunnel>>();
		for (BWRJzFluxFunnel vo : rsList) {
			String busiLine = vo.getBusiLine();
			busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			List<BWRJzFluxFunnel> tempList = new ArrayList<BWRJzFluxFunnel>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
			
			if("quanguo".equals(vo.getType())){
				vo.setType("全国");
			}
			
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}
		return rsMap;
	
	}

	@Override
	public Map<String, List<BWRJzFluxFunnel>> getBWRJzFluxFunnelCateMap(
			List<String> dateList) throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();	
		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);
			
			for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
				String busiName = it.next();
					
				if("all".equals(busiName) || "ershouche".equals(busiName)){ //总体和二手车不看
					continue;
				}
				
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" disp_cate2_name, SUM(cost/100)/COUNT(DISTINCT stat_date) as '消费', ");
				sql.append(" sum(pv/10000)/COUNT(DISTINCT stat_date) as '频道pv(W)', ");
				sql.append(" SUM(adpv)/SUM(pv) as 'PVR', ");
				sql.append(" SUM(disp/10000)/COUNT(DISTINCT stat_date) as 'ADIMP(W)', ");
				sql.append(" SUM(disp)/SUM(adpv) as 'ASN', ");
				sql.append(" SUM(click)/COUNT(DISTINCT stat_date) as '点击量', ");
				sql.append(" SUM(click)/SUM(pv) as 'CTR1', ");
				sql.append(" SUM(click)/SUM(disp) as 'CTR2', ");
				sql.append(" SUM(click)/SUM(adpv) as 'CTR3' ");
				sql.append(" from (");
				sql.append(" SELECT stat_date,disp_cate2_name ,sum(cost) cost,sum(pv) pv,sum(adpv) adpv,");
				sql.append(" sum(disp) disp,sum(click) click ");
				sql.append(" from t_jingzhun_city_cate_stat_new_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");
				if("ershouchuangxin".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) + " and disp_cate2_name is not null ");
				}else if("zhaoshangjiameng".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) + " and disp_cate2_name is not null ");
				}else{
						sql.append(" and business_type_biz ='" + busiName +"' ");
				}
				
				if("huangye".equals(busiName)){
					sql.append(" and disp_cate2_name in ('搬家', '租车', '工商注册','投资担保','家装服务','家电维修','保洁清洗')  ");
				}else if("zhaopin".equals(busiName)){
					sql.append(" and disp_cate2_name in ('销售', '普工/技工', '客服', '餐饮', '超市/百货/零售', '人事/行政/后勤', '房产中介', '美容/美发', '娱乐/休闲', '物流/仓储') ");
				}else if("ershou".equals(busiName)){
					sql.append(" and disp_cate2_name in ('二手手机', '宠物狗',  '二手家具', '二手家电', '二手摩托车', '二手设备', '服装/鞋帽/箱包', '办公用品/设备', '自行车/电动车') ");
				}else if("fangchan".equals(busiName)){
					sql.append(" and disp_cate2_name in ('合租房','求租房','二手房','写字楼','商铺租售/生意转让','厂房/仓库/土地/车位','租房') ");
				}
				
				sql.append(" and disp_cate2_name is not null");
				sql.append(" GROUP BY stat_date,disp_cate2_name ");
				
				if(monthList.size() > 1){
					sql.append(" UNION all  ");
					sql.append(" SELECT stat_date,disp_cate2_name ,sum(cost) cost,sum(pv) pv,sum(adpv) adpv,");
					sql.append(" sum(disp) disp,sum(click) click ");
					sql.append(" from t_jingzhun_city_cate_stat_new_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) + " and disp_cate2_name is not null ");
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) + " and disp_cate2_name is not null ");
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					if("huangye".equals(busiName)){
						sql.append(" and disp_cate2_name in ('搬家', '租车', '工商注册','投资担保','家装服务','家电维修','保洁清洗')  ");
					}else if("zhaopin".equals(busiName)){
						sql.append(" and disp_cate2_name in ('销售', '普工/技工', '客服', '餐饮', '超市/百货/零售', '人事/行政/后勤', '房产中介', '美容/美发', '娱乐/休闲', '物流/仓储') ");
					}else if("ershou".equals(busiName)){
						sql.append(" and disp_cate2_name in ('二手手机', '宠物狗',  '二手家具', '二手家电', '二手摩托车', '二手设备', '服装/鞋帽/箱包', '办公用品/设备', '自行车/电动车') ");
					}else if("fangchan".equals(busiName)){
						sql.append(" and disp_cate2_name in ('合租房','求租房','二手房','写字楼','商铺租售/生意转让','厂房/仓库/土地/车位','租房') ");
					}
									
					sql.append(" and disp_cate2_name is not null");
					sql.append(" GROUP BY stat_date,disp_cate2_name ");
				}
				sql.append(" ) a");
				sql.append(" GROUP BY disp_cate2_name ");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("busiLine");
				cols1.add("type");
				cols1.add("cost");
				cols1.add("pv");
				cols1.add("PVR");
				cols1.add("adimp");
				cols1.add("ASN");
				cols1.add("click");
				cols1.add("CTR1");
				cols1.add("CTR2");
				cols1.add("CTR3");
				
				sqlMap.put(sql.toString(), cols1);
				
			}
		
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("type");
		
		List<BWRJzFluxFunnel> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRJzFluxFunnel.class, gbList);
		Map<String,List<BWRJzFluxFunnel>>  rsMap = new HashMap<String, List<BWRJzFluxFunnel>>();
		for (BWRJzFluxFunnel vo : rsList) {
			String busiLine = vo.getBusiLine();
			busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			List<BWRJzFluxFunnel> tempList = new ArrayList<BWRJzFluxFunnel>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
			
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}
		return rsMap;
	
	}

	@Override
	public List<WRCusFunnelVo> cusBanlanceDatas(List<String> dateList,String channel)
			throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		
		StringBuffer sql = new StringBuffer();
		
		HashMap<String, String> busLineConditionMap = MyStore.getBusLineConditionMap();
		
		List<String> typeList = new ArrayList<String>();
		typeList.add("总体");
		typeList.add("会员");
		typeList.add("非会员");
		for (String searchDate : dateList) {
			String month = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
			for (String  type : typeList) {
				//余额总客户数   余额大于50客户数 现金余额>0客户数 现金余额>50客户数
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");cols1.add("type");
				cols1.add("allCus");cols1.add("left0");cols1.add("left50");
				cols1.add("cashLeft0");cols1.add("cashLeft50");
				sql.delete(0, sql.length()+1);
				sql.append(" SELECT  '"+searchDate+"',");
				//infobright 中文问题哎....字符占位 后期处理
				if("总体".equals(type)){
					sql.append(" 'A',COUNT(DISTINCT user_id) AS '余额客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance + adgift_balance > 0 THEN user_id END) AS '余额>0客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN (adcash_balance + adgift_balance)/100 > 50 THEN user_id END) AS '余额>50客户数',  ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance > 0 THEN user_id END) AS '现金余额>0客户数',  ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance/100 > 50 THEN user_id END) AS '现金余额>50客户数'   ");
				}else if("会员".equals(type)){
					sql.append(" 'B',COUNT(DISTINCT CASE WHEN is_vip = 1 then user_id end) AS '会员余额客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance + adgift_balance > 0 and is_vip = 1 THEN user_id END) AS '会员余额>0客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN (adcash_balance + adgift_balance)/100 > 50 and is_vip = 1 THEN user_id END) AS '会员余额>50客户数' , ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance > 0 and is_vip = 1 THEN user_id END) AS '会员现金余额>0客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance/100 > 50 and is_vip = 1 THEN user_id END) AS '会员现金余额>50客户数'  ");
					
				}else if("非会员".equals(type)){
					sql.append(" 'C',COUNT(DISTINCT CASE WHEN is_vip = 0 or is_vip is null then user_id end) AS '非会员余额客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance + adgift_balance > 0 and (is_vip = 0 or is_vip is null) THEN user_id END) AS '非会员余额>0客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN (adcash_balance + adgift_balance)/100 > 50 and (is_vip = 0 or is_vip is null) THEN user_id END) AS '非会员余额>50客户数',  ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance  > 0 and (is_vip = 0 or is_vip is null) THEN user_id END) AS '非会员现金余额>0客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance/100  > 50 and (is_vip = 0 or is_vip is null) THEN user_id END) AS '非会员现金余额>50客户数'  ");
				}
				sql.append(" FROM t_common_user_account_new_" + month);
				sql.append(" WHERE stat_date = '"+searchDate+"'  ");
				
				if(busLineConditionMap.containsKey(channel)){
					sql.append(" and " + busLineConditionMap.get(channel).replace("disp_cate1_name", "source_cate1_name").replace("disp_cate2_name", "source_cate2_name"));
				}
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				sqlMap.put(sql.toString(), cols1);
				
				
				//精准 置顶 智能 客户数
				//判断是否跨月
				String searchBeginDate =DateUtil.getStringDate(DateUtil.getDateBefore(DateUtil.strToDate(searchDate, "yyyy-MM-dd"), 6), "yyyy-MM-dd") ;
				String searchEndDate = searchDate;
				List<String> monthList =  DateUtil.subSearchDate(searchBeginDate, searchEndDate);
				boolean isTwoMonth = false;
				if(monthList.size() == 2){
					isTwoMonth = true;
				}
				
				List<String>  incomeTypeList = new ArrayList<String>();
				incomeTypeList.add("jingzhun");
				incomeTypeList.add("zhiding");
				incomeTypeList.add("zhineng");
				for (String incomeType : incomeTypeList) {
					List<String> cols2 = new ArrayList<String>();
					cols2.add("statDate");cols2.add("type");
					
					if("jingzhun".equals(incomeType)){
						cols2.add("preCus");
					}else if("zhiding".equals(incomeType)){
						cols2.add("topCus");
					}else if("zhineng".equals(incomeType)){
						cols2.add("idCus");
					}
					
					

					StringBuffer conditions = new StringBuffer();
					conditions.append(" where stat_date  <='"+searchEndDate+"'  and stat_date >='"+searchBeginDate+"'  ");
					
					if(busLineConditionMap.containsKey(channel)){
						conditions.append(" and " + busLineConditionMap.get(channel));
					}else{
						System.out.println(" channel " + channel +" can not find in busLineConditionMap...1");
					}
					
					
					sql.delete(0, sql.length()+1);
					sql.append(" SELECT  '"+searchDate+"',");
					if("总体".equals(type)){
						sql.append(" 'A', ");
					}else if("会员".equals(type)){
						sql.append(" 'B', ");
					}else if("非会员".equals(type)){
						sql.append(" 'C', ");
					}
					sql.append(" sum(kh)/count(distinct stat_date) from ( ");
					
					sql.append("SELECT  stat_date,");
					if("总体".equals(type)){
						sql.append("COUNT(DISTINCT user_id)  kh");
					}else if("会员".equals(type)){
						sql.append("COUNT(DISTINCT case when is_vip=1 then user_id end)  kh");
					}else if("非会员".equals(type)){
						sql.append("COUNT(DISTINCT case when is_vip=0 then user_id end)  kh");
					}
					sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
					sql.append(" " + conditions.toString() );
					sql.append(" GROUP BY stat_date ");
					if(isTwoMonth){ //跨月
						sql.append(" union all  ");
						
						sql.append("SELECT  stat_date,");
						if("总体".equals(type)){
							sql.append("COUNT(DISTINCT user_id)  kh");
						}else if("会员".equals(type)){
							sql.append("COUNT(DISTINCT case when is_vip=1 then user_id end)  kh");
						}else if("非会员".equals(type)){
							sql.append("COUNT(DISTINCT case when is_vip=0 then user_id end)  kh");
						}
						sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
						sql.append(" " + conditions.toString() );
						sql.append(" GROUP BY stat_date ");
					}
					sql.append(") a");
					
					sql.append("  :"+DBType.db_94_data_app.getValue());
					
					sqlMap.put(sql.toString(), cols2);
				}
				
			}
		}
		
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("type");
		
		List<WRCusFunnelVo> tempList =  comDeal.AssembledDataBySqlMap(sqlMap, WRCusFunnelVo.class, gbList);
		//计算精准 置顶 智能 客户占比数
		for (WRCusFunnelVo vo : tempList) {
			String type = vo.getType();
			if("A".equals(type)){
				vo.setType("总体");
			}else if("B".equals(type)){
				vo.setType("会员");
			}else if("C".equals(type)){
				vo.setType("非会员");
			}
			if(vo.getAllCus() > 0 ){
				vo.setPreRate((double)vo.getPreCus()/vo.getAllCus());
				vo.setTopRate((double)vo.getTopCus()/vo.getAllCus());
				vo.setIdRate((double)vo.getIdCus()/vo.getAllCus());
			}
		}
		return tempList;
	}

	@SuppressWarnings({ "static-access", "rawtypes" })
	@Override
	public List<String> consumCycleData(String yesterday, String lastWeek,
			String channel, String cate1, String cate2) throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		
		//lastWeek日期处理下 为了和以前的保持一致  要多加一天
		lastWeek =DateUtil.getStringDate(DateUtil.getDateAfter(DateUtil.strToDate(lastWeek, "yyyy-MM-dd"), 1), "yyyy-MM-dd") ;
		
		String month = yesterday.substring(0,yesterday.lastIndexOf("-")).replace("-", "");
		
		StringBuffer sql = new StringBuffer();
		
		List<String> cols1 = new ArrayList<String>();  //余额
		cols1.add("city");
		cols1.add("cashAccountTotal");
		
		List<String> cols2 = new ArrayList<String>();  //消耗
		cols2.add("city");
		cols2.add("dayConsumTotal");
		if(!"".equals(cate2)){
			for(int i = 1 ; i <= cate2.split(",").length ; i++){
				cols1.add("cashAccount" + i);
				cols2.add("dayConsum" + i);
			}
		}
		
		/**获取昨天所有账户余额   以及 消耗 
		注：1.二手车的查询类别是  cate2 = '二手车'
		      2.二手车、招聘 只需要查询总体数据
		      3.房产的品类数据 需用like 租房、二手房 进行CASE WHEN SUM
		*/
		
		//全国余额
		sql.delete(0, sql.length()+1);
		sql.append(" select 'all', sum(adcash_balance)/1000000 ");
		if(!"".equals(cate2)){
			for(String cate : cate2.split(",")){
				if(cate.contains("%")){
					sql.append(" ,sum(case when source_cate2_name like '"+ cate +"' then adcash_balance end)/1000000 ");
				}else{
					sql.append(" ,sum(case when source_cate2_name = '"+ cate +"' then adcash_balance end)/1000000 ");
				}
			}
		}
		sql.append(" from t_common_user_account_new_" + month );
		sql.append(" where stat_date = '"+yesterday+"'");
		sql.append(" and is_vip=1 ");
		sql.append(" and " + cate1);
		sql.append(" and source_city1_name in (" + MyStore.zyCitys + ") ");
		sql.append(" and user_id not in ( select user_id from bigcustomer_id )  ");
		sql.append(" union ");
		//分城市余额
		sql.append(" select source_city1_name, sum(adcash_balance)/1000000 ");
		if(!"".equals(cate2)){
			for(String cate : cate2.split(",")){
				if(cate.contains("%")){
					sql.append(" ,sum(case when source_cate2_name like '"+ cate +"' then adcash_balance end)/1000000 ");
				}else{
					sql.append(" ,sum(case when source_cate2_name = '"+ cate +"' then adcash_balance end)/1000000 ");
				}
			}
		}
		sql.append(" from t_common_user_account_new_" + month );
		sql.append(" where stat_date = '"+yesterday+"'");
		sql.append(" and is_vip=1");
		sql.append(" and " + cate1);
		sql.append(" and source_city1_name in (" + MyStore.zyCitys + ") ");
		sql.append(" and user_id not in ( select user_id from bigcustomer_id )  ");
		sql.append(" group by source_city1_name ");
		sql.append(" :"+ DBType.db_94_data_app.getValue());
		sqlMap.put(sql.toString(),cols1);
		
		
		//消耗
		List<String> dateList = DateUtil.getDateList(lastWeek, yesterday);
		for (String searchDate : dateList) {
			String costMonth = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
			sql.delete(0, sql.length()+1);
			sql.append(" select 'all', sum(adcash_consume)/100 ");
			if(!"".equals(cate2)){
				for(String cate : cate2.split(",")){
					if(cate.contains("%")){
						sql.append(" ,sum(case when source_cate2_name like '"+ cate +"' then adcash_consume/100 end) ");
					}else{
						sql.append(" ,sum(case when source_cate2_name = '"+ cate +"' then adcash_consume/100 end) ");
					}
				}
			}
			sql.append(" from t_common_user_account_new_" + costMonth);
			sql.append(" where stat_date = '"+searchDate+"'");
			sql.append(" and is_vip=1 ");
			sql.append(" and " + cate1);
			sql.append(" and source_city1_name in (" + MyStore.zyCitys + ") ");
			sql.append(" and user_id not in ( select user_id from bigcustomer_id )  ");
			sql.append(" union ");
			sql.append(" select source_city1_name, sum(adcash_consume)/100 ");
			if(!"".equals(cate2)){
				for(String cate : cate2.split(",")){
					if(cate.contains("%")){
						sql.append(" ,sum(case when source_cate2_name like '"+ cate +"' then adcash_consume/100 end) ");
					}else{
						sql.append(" ,sum(case when source_cate2_name = '"+ cate +"' then adcash_consume/100 end) ");
					}
				}
			}
			sql.append(" from t_common_user_account_new_" + costMonth);
			sql.append(" where stat_date = '"+searchDate+"'");
			sql.append(" and is_vip=1");
			sql.append(" and " + cate1);
			sql.append(" and source_city1_name in (" + MyStore.zyCitys + ") ");
			sql.append(" and user_id not in ( select user_id from bigcustomer_id )  ");
			sql.append(" group by source_city1_name ");
			sql.append(" :"+ DBType.db_94_data_app.getValue());
			
			sqlMap.put(sql.toString(),cols2);
		}
	
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("city");
		
		List<WRConsumCycleVo> tempList = comDeal.AssembledDataBySqlMap(sqlMap, WRConsumCycleVo.class, gbList);
		
		//计算天数  可能存在某天没有数的情况，不能算日均时除以7
		sql.delete(0, sql.length()+1);		
		
		//跨月检查  
		List<String> monthList = checkTwoMonth(yesterday);
		
		sql.append(" select sum(day)");
		sql.append(" from (");
		sql.append(" select count(DISTINCT stat_date) as day FROM t_common_user_account_new_" + monthList.get(0).split(";")[0]);
		sql.append(" where stat_date >= date_sub('"+yesterday+"',interval 6 day)  ");
		sql.append(" and stat_date <= '"+yesterday+ "' ");
		
		if(monthList.size() > 1){
			sql.append(" UNION all  ");
			sql.append(" select count(DISTINCT stat_date) as day FROM t_common_user_account_new_" + monthList.get(1).split(";")[0]);
			sql.append(" where stat_date >= date_sub('"+yesterday+"',interval 6 day)  ");
			sql.append(" and stat_date <= '"+yesterday+ "' ");		
		}
		sql.append(" ) a");
				
		sql.append(" :"+ DBType.db_94_data_app.getValue());
		
		List<String> cols3 = new ArrayList<String>();  
		cols3.add("dayConsumTotal");
		
		Map<String,List<String>> sqlMap1 = new HashMap<String,List<String>>();
		sqlMap1.put(sql.toString(),cols3);
		List<WRConsumCycleVo> tempList1 = comDeal.AssembledDataBySqlMap(sqlMap1, WRConsumCycleVo.class, gbList);
		
		double x=tempList1.get(0).getDayConsumTotal();
				
		
		List<String> finalList = new ArrayList<String>();
		Class clazz = WRConsumCycleVo.class;
		DecimalFormat df = new DecimalFormat("0.00");
		for (WRConsumCycleVo vo : tempList) {
			StringBuffer value = new StringBuffer();
			if("all".equals(vo.getCity())){
				vo.setCity("全国");
			}
			value.append(vo.getCity() + ";");
			
			//总计数据
			double accountTotal =  vo.getCashAccountTotal();
			double consumTotal =  vo.getDayConsumTotal()/x;
			int costDaysTotal = (int)(accountTotal*10000 / consumTotal);
			value.append(df.format(accountTotal) +";");
			value.append(df.format(consumTotal) +";");
			value.append(costDaysTotal +";");
			//二级类数据
			if(!"".equals(cate2)){
				for(int i = 1 ; i <= cate2.split(",").length ; i++){
					String accountName = "cashAccount" + i;
					String consumName = "dayConsum" + i;
					double account = Double.parseDouble(reflectUtil.getGetterMethod(clazz, clazz.getDeclaredField(accountName)).invoke(vo).toString());
					double consum = Double.parseDouble(reflectUtil.getGetterMethod(clazz, clazz.getDeclaredField(consumName)).invoke(vo).toString())/x;
					String costDays = "--";
					if( consum > 0){
						costDays = String.valueOf((int)(account*10000 / consum));
					}
					value.append(df.format(account) +";");
					value.append(df.format(consum) +";");
					value.append(costDays +";");
				}
			}
			
			finalList.add(value.toString());
		}
		return finalList;
	}

	@Override
	public Map<String, List<BWRLineCityCate>> cityDatas(List<String> dateList)
			throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		
		List<String> incomTypeList = new ArrayList<String>();
		incomTypeList.add("jingzhun");
		incomTypeList.add("zhiding");
		incomTypeList.add("zhineng");
		
		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);
			
			for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
				String busiName = it.next();
				
				if("all".equals(busiName)){ //总体不看
					continue;
				}
		
				//精准 置顶 智能 收入数据
				for(String incomeType : incomTypeList){
					sql.delete(0, sql.length()+1);
					sql.append(" select '" + searchDate + "','"+busiName+"',");
					sql.append(" disp_city1_name,avg(a) as '消耗', avg(u) as '客户数' ");
					sql.append(" from (");
					sql.append(" select stat_date, disp_city1_name, sum(cash)/100 a,  ");
					sql.append(" count(distinct case when cost>0 then  user_id end) u  ");
					sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");

					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");
					sql.append(" group by 1,2 ");
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select stat_date, disp_city1_name, sum(cash)/100 a,  ");
						sql.append(" count(distinct case when cost>0 then  user_id end) u  ");
						sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");

						if("ershouchuangxin".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");
						sql.append(" group by 1,2 ");
					}
					sql.append(" ) a");
					sql.append(" GROUP BY disp_city1_name ");
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("busiLine");
					cols1.add("type");
					
					if("jingzhun".equals(incomeType)){
						cols1.add("jzCash");
						cols1.add("jzCusCount");
					}else if("zhiding".equals(incomeType)){
						cols1.add("zdCash");
						cols1.add("zdCusCount");
					}else if("zhineng".equals(incomeType)){
						cols1.add("znCash");
						cols1.add("znCusCount");
					}
					
					sqlMap.put(sql.toString(), cols1);
				}
			
				//城市三端总PV
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" disp_city1_name,sum(a.pv)/count(distinct stat_date) as '总PV' ");
				sql.append(" from (");
				//PC
				sql.append(" select stat_date,disp_city1_name,sum(pv) as pv");
				sql.append(" from t_netflow_pc_city_cate_pv_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");
				sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");

				if("ershouchuangxin".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
				}else if("zhaoshangjiameng".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
				}else{
						sql.append(" and business_type ='" + busiName +"' ");
				}
				
				sql.append(" and page_type='all' ");
				if("zhaopin".equals(busiName)){
					sql.append(" and disp_cate1_name in ('全职招聘','兼职招聘') ");
					sql.append(" and disp_cate2_name='all' ");
				}else if(!busiName.equals("ershouchuangxin") && !busiName.equals("zhaoshangjiameng")){
					sql.append(" and disp_cate1_name='all'  ");
				}
				sql.append(" group by stat_date,disp_city1_name ");
				if(monthList.size() > 1){
					sql.append(" union all ");
					sql.append(" select stat_date,disp_city1_name,sum(pv) as pv");
					sql.append(" from t_netflow_pc_city_cate_pv_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");

					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
					}else{
							sql.append(" and business_type ='" + busiName +"' ");
					}
					
					sql.append(" and page_type='all' ");
					if("zhaopin".equals(busiName)){
						sql.append(" and disp_cate1_name in ('全职招聘','兼职招聘') ");
						sql.append(" and disp_cate2_name='all' ");
					}else if(!busiName.equals("ershouchuangxin") && !busiName.equals("zhaoshangjiameng")){
						sql.append(" and disp_cate1_name='all'  ");
					}
					
					sql.append(" group by stat_date,disp_city1_name ");
				}
				//M
				sql.append(" union all ");
				sql.append(" select stat_date,disp_city1_name,sum(pv) as pv ");
				sql.append(" from t_netflow_m_city_cate_pv_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");
				sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");
				
				if("ershouchuangxin".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
				}else if("zhaoshangjiameng".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
				}else{
						sql.append(" and business_type ='" + busiName +"' ");
				}
				
				sql.append(" and page_type='all' ");
				if("zhaopin".equals(busiName)){
					sql.append(" and disp_cate1_name in ('全职招聘','兼职招聘') ");
					sql.append(" and disp_cate2_name='all' ");
				}else if(!busiName.equals("ershouchuangxin") && !busiName.equals("zhaoshangjiameng")){
					sql.append(" and disp_cate1_name='all'  ");
				}
				
				sql.append(" group by stat_date,disp_city1_name ");
				if(monthList.size() > 1){
					sql.append(" union all ");
					sql.append(" select stat_date,disp_city1_name,sum(pv) as pv ");
					sql.append(" from t_netflow_m_city_cate_pv_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");
					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
					}else{
							sql.append(" and business_type ='" + busiName +"' ");
					}
					
					sql.append(" and page_type='all' ");
					if("zhaopin".equals(busiName)){
						sql.append(" and disp_cate1_name in ('全职招聘','兼职招聘') ");
						sql.append(" and disp_cate2_name='all' ");
					}else if(!busiName.equals("ershouchuangxin") && !busiName.equals("zhaoshangjiameng")){
						sql.append(" and disp_cate1_name='all'  ");
					}
					
					sql.append(" group by stat_date,disp_city1_name ");
				}
				//APP
				sql.append(" union all ");
				sql.append(" select stat_date,disp_city1_name,sum(pv) as pv ");
				sql.append(" from t_netflow_app_pv_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");
				sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");
				
				if("ershouchuangxin".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
				}else if("zhaoshangjiameng".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
				}else{
						sql.append(" and business_type ='" + busiName +"' ");
				}
				
				sql.append(" and page_type='all' ");
				if("zhaopin".equals(busiName)){
					sql.append(" and disp_cate1_name in ('全职招聘','兼职招聘') ");
					sql.append(" and disp_cate2_name='all' ");
				}else if(!busiName.equals("ershouchuangxin") && !busiName.equals("zhaoshangjiameng")){
					sql.append(" and disp_cate1_name='all'  ");
				}
				
				
				sql.append(" group by stat_date,disp_city1_name ");
				if(monthList.size() > 1){
					sql.append(" union all ");
					sql.append(" select stat_date,disp_city1_name,sum(pv) as pv ");
					sql.append(" from t_netflow_app_pv_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");
					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
					}else{
							sql.append(" and business_type ='" + busiName +"' ");
					}
					
					sql.append(" and page_type='all' ");
					if("zhaopin".equals(busiName)){
						sql.append(" and disp_cate1_name in ('全职招聘','兼职招聘') ");
						sql.append(" and disp_cate2_name='all' ");
					}else if(!busiName.equals("ershouchuangxin") && !busiName.equals("zhaoshangjiameng")){
						sql.append(" and disp_cate1_name='all'  ");
					}
					sql.append(" group by stat_date,disp_city1_name ");
				}
				sql.append(" )a ");
				sql.append(" group by disp_city1_name ");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols2 = new ArrayList<String>();
				cols2.add("statDate");
				cols2.add("busiLine");
				cols2.add("type");
				cols2.add("allPv");
				sqlMap.put(sql.toString(), cols2);
			
			
			}
		
			
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("type");
		
		List<BWRLineCityCate> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineCityCate.class, gbList);
		Map<String,List<BWRLineCityCate>>  rsMap = new HashMap<String, List<BWRLineCityCate>>();
		for (BWRLineCityCate vo : rsList) {
			String busiLine = vo.getBusiLine();
			busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			List<BWRLineCityCate> tempList = new ArrayList<BWRLineCityCate>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
			
			//计算 流量变现率 和 客户充足度     
			//流量变现率 =  精准置顶智能现金SUM/城市总PV
			//客户充足度 =  精准置顶智能客户数SUM/城市总PV
			if(vo.getAllPv()==0)
			{
				vo.setFluxTranRate(0);
				vo.setCusAdequacy(0);
			}
			else
			{
				vo.setFluxTranRate((double)(vo.getJzCash() + vo.getZdCash() + vo.getZnCash()) / vo.getAllPv()) ;
				vo.setCusAdequacy((double)(vo.getJzCusCount() + vo.getZdCusCount() + vo.getZnCusCount()) / vo.getAllPv()) ;
			}
		
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}
		return rsMap;
	
	}

	@Override
	public Map<String, List<BWRLineCityCate>> cateDatas(List<String> dateList)
			throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		
		List<String> incomTypeList = new ArrayList<String>();
		incomTypeList.add("jingzhun");
		incomTypeList.add("zhiding");
		incomTypeList.add("zhineng");
		
		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);			
			for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
				String busiName = it.next();					
				
				if("all".equals(busiName) || "ershouche".equals(busiName)){ //总体 、二手车不看
					continue;				
				}		
			
				//精准 置顶 智能 收入数据
				for(String incomeType : incomTypeList){
					sql.delete(0, sql.length()+1);
					sql.append(" select '" + searchDate + "','"+busiName+"',");
					sql.append(" disp_cate2_name,avg(a) as '消耗', avg(u) as '客户数' ");
					sql.append(" from (");
					sql.append(" select stat_date, disp_cate2_name, sum(cash)/100 a,  ");
					sql.append(" count(distinct case when cost>0 then  user_id end) u  ");
					sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					sql.append(" and cost > 0");
					if("huangye".equals(busiName)){
						sql.append(" and disp_cate2_name in ('搬家','租车','工商注册','投资担保','家装服务','家电维修','保洁清洗','保姆/月嫂','艺术培训','管道疏通','婚庆','餐饮加盟','开锁/换锁/修锁','电脑维修','房屋维修/防水','货运物流','驾校','建材','工装服务','职业技能培训','二手回收') ");
					}else if("zhaopin".equals(busiName)){
						sql.append(" and disp_cate1_name in('全职招聘','兼职招聘')");
						sql.append(" and disp_cate2_name  in ('销售','普工/技工','餐饮','人事/行政/后勤','客服','司机/交通服务','物流/仓储','家政保洁/安保','美容/美发','淘宝职位','学生兼职','计算机/互联网/通信','娱乐/休闲','保健按摩','美术/设计/创意','房产中介','超市/百货/零售','教育培训') ");
					}else if("ershouchuangxin".equals(busiName)){
						sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null ");
					}else if("zhaoshangjiameng".equals(busiName)){
						sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null ");
					}else if("fangchan".equals(busiName)){
						sql.append(" and disp_cate2_name in ('求租房','二手房','写字楼','商铺租售/生意转让','厂房/仓库/土地/车位','租房','短租房/日租房','合租房') ");
					}
					
					sql.append(" group by 1,2 ");
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select stat_date, disp_cate2_name, sum(cash)/100 a,  ");
						sql.append(" count(distinct case when cost>0 then  user_id end) u  ");
						sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");
						sql.append(" and cost > 0");
						if("huangye".equals(busiName)){
							sql.append(" and disp_cate2_name in ('搬家','租车','工商注册','投资担保','家装服务','家电维修','保洁清洗','保姆/月嫂','艺术培训','管道疏通','婚庆','餐饮加盟','开锁/换锁/修锁','电脑维修','房屋维修/防水','货运物流','驾校','建材','工装服务','职业技能培训','二手回收') ");
						}else if("zhaopin".equals(busiName)){
							sql.append(" and disp_cate1_name in('全职招聘','兼职招聘')");
							sql.append(" and disp_cate2_name  in ('销售','普工/技工','餐饮','人事/行政/后勤','客服','司机/交通服务','物流/仓储','家政保洁/安保','美容/美发','淘宝职位','学生兼职','计算机/互联网/通信','娱乐/休闲','保健按摩','美术/设计/创意','房产中介','超市/百货/零售','教育培训') ");
						}else if("ershouchuangxin".equals(busiName)){
							sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null ");
						}else if("zhaoshangjiameng".equals(busiName)){
							sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null ");
						}else if("fangchan".equals(busiName)){
							sql.append(" and disp_cate2_name in ('求租房','二手房','写字楼','商铺租售/生意转让','厂房/仓库/土地/车位','租房','短租房/日租房','合租房') ");
						}					
						sql.append(" group by 1,2 ");
					}
					sql.append(" ) a");
					sql.append(" GROUP BY disp_cate2_name ");
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("busiLine");
					cols1.add("type");
					
					if("jingzhun".equals(incomeType)){
						cols1.add("jzCash");
						cols1.add("jzCusCount");
					}else if("zhiding".equals(incomeType)){
						cols1.add("zdCash");
						cols1.add("zdCusCount");
					}else if("zhineng".equals(incomeType)){
						cols1.add("znCash");
						cols1.add("znCusCount");
					}
					
					sqlMap.put(sql.toString(), cols1);
			}
				
				//城市三端总PV
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" disp_cate2_name,sum(a.pv)/count(distinct stat_date) as '总PV' ");
				sql.append(" from (");
				//PC
				sql.append(" select stat_date,disp_cate2_name,sum(pv) as pv");
				sql.append(" from t_netflow_pc_city_cate_pv_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");
				sql.append(" and pv > 0");
				
				if("huangye".equals(busiName)){
					sql.append(" and disp_cate2_name in ('搬家','租车','工商注册','投资担保','家装服务','家电维修','保洁清洗','保姆/月嫂','艺术培训','管道疏通','婚庆','餐饮加盟','开锁/换锁/修锁','电脑维修','房屋维修/防水','货运物流','驾校','建材','工装服务','职业技能培训','二手回收') ");
				}else if("zhaopin".equals(busiName)){
					sql.append(" and disp_cate1_name in('全职招聘','兼职招聘')");
					sql.append(" and disp_cate2_name  in ('销售','普工/技工','餐饮','人事/行政/后勤','客服','司机/交通服务','物流/仓储','家政保洁/安保','美容/美发','淘宝职位','学生兼职','计算机/互联网/通信','娱乐/休闲','保健按摩','美术/设计/创意','房产中介','超市/百货/零售','教育培训') ");
				}else if("ershouchuangxin".equals(busiName)){
					sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null and business_type ='all'");
				}else if("zhaoshangjiameng".equals(busiName)){
					sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null and business_type ='all'");
				}else if("fangchan".equals(busiName)){
					sql.append(" and disp_cate2_name in ('求租房','二手房','写字楼','商铺租售/生意转让','厂房/仓库/土地/车位','租房','短租房/日租房','合租房') ");
				}
					
				if(!busiName.equals("ershouchuangxin") && !busiName.equals("zhaoshangjiameng")){
					sql.append(" and business_type='"+busiName+"' ");
				}
				
				sql.append(" and page_type='all' ");
				sql.append(" and disp_city1_name='all'  ");
				sql.append(" group by stat_date,disp_cate2_name ");
				if(monthList.size() > 1){
					sql.append(" union all ");
					sql.append(" select stat_date,disp_cate2_name,sum(pv) as pv");
					sql.append(" from t_netflow_pc_city_cate_pv_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					sql.append(" and pv > 0");
					if("huangye".equals(busiName)){
						sql.append(" and disp_cate2_name in ('搬家','租车','工商注册','投资担保','家装服务','家电维修','保洁清洗','保姆/月嫂','艺术培训','管道疏通','婚庆','餐饮加盟','开锁/换锁/修锁','电脑维修','房屋维修/防水','货运物流','驾校','建材','工装服务','职业技能培训','二手回收') ");
					}else if("zhaopin".equals(busiName)){
						sql.append(" and disp_cate1_name in('全职招聘','兼职招聘')");
						sql.append(" and disp_cate2_name  in ('销售','普工/技工','餐饮','人事/行政/后勤','客服','司机/交通服务','物流/仓储','家政保洁/安保','美容/美发','淘宝职位','学生兼职','计算机/互联网/通信','娱乐/休闲','保健按摩','美术/设计/创意','房产中介','超市/百货/零售','教育培训') ");
					}else if("ershouchuangxin".equals(busiName)){
						sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null and business_type ='all'");
					}else if("zhaoshangjiameng".equals(busiName)){
						sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null and business_type ='all'");
					}else if("fangchan".equals(busiName)){
						sql.append(" and disp_cate2_name in ('求租房','二手房','写字楼','商铺租售/生意转让','厂房/仓库/土地/车位','租房','短租房/日租房','合租房') ");
					}
						
					if(!busiName.equals("ershouchuangxin") && !busiName.equals("zhaoshangjiameng")){
						sql.append(" and business_type='"+busiName+"' ");
					}
					sql.append(" and page_type='all' ");
					sql.append(" and disp_city1_name='all'  ");
					sql.append(" group by stat_date,disp_cate2_name ");
				}
				//M
				sql.append(" union all ");
				sql.append(" select stat_date,disp_cate2_name,sum(pv) as pv ");
				sql.append(" from t_netflow_m_city_cate_pv_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");
				sql.append(" and pv > 0");
				if("huangye".equals(busiName)){
					sql.append(" and disp_cate2_name in ('搬家','租车','工商注册','投资担保','家装服务','家电维修','保洁清洗','保姆/月嫂','艺术培训','管道疏通','婚庆','餐饮加盟','开锁/换锁/修锁','电脑维修','房屋维修/防水','货运物流','驾校','建材','工装服务','职业技能培训','二手回收') ");
				}else if("zhaopin".equals(busiName)){
					sql.append(" and disp_cate1_name in('全职招聘','兼职招聘')");
					sql.append(" and disp_cate2_name  in ('销售','普工/技工','餐饮','人事/行政/后勤','客服','司机/交通服务','物流/仓储','家政保洁/安保','美容/美发','淘宝职位','学生兼职','计算机/互联网/通信','娱乐/休闲','保健按摩','美术/设计/创意','房产中介','超市/百货/零售','教育培训') ");
				}else if("ershouchuangxin".equals(busiName)){
					sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null and business_type ='all'");
				}else if("zhaoshangjiameng".equals(busiName)){
					sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null and business_type ='all'");
				}else if("fangchan".equals(busiName)){
					sql.append(" and disp_cate2_name in ('求租房','二手房','写字楼','商铺租售/生意转让','厂房/仓库/土地/车位','租房','短租房/日租房','合租房') ");
				}
					
				if(!busiName.equals("ershouchuangxin") && !busiName.equals("zhaoshangjiameng")){
					sql.append(" and business_type='"+busiName+"' ");
				}
				sql.append(" and page_type='all' ");
				sql.append(" and disp_city1_name='all'  ");
				sql.append(" group by stat_date,disp_cate2_name ");
				
				if(monthList.size() > 1){
					sql.append(" union all ");
					sql.append(" select stat_date,disp_cate2_name,sum(pv) as pv ");
					sql.append(" from t_netflow_m_city_cate_pv_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					sql.append(" and pv > 0");
					if("huangye".equals(busiName)){
						sql.append(" and disp_cate2_name in ('搬家','租车','工商注册','投资担保','家装服务','家电维修','保洁清洗','保姆/月嫂','艺术培训','管道疏通','婚庆','餐饮加盟','开锁/换锁/修锁','电脑维修','房屋维修/防水','货运物流','驾校','建材','工装服务','职业技能培训','二手回收') ");
					}else if("zhaopin".equals(busiName)){
						sql.append(" and disp_cate1_name in('全职招聘','兼职招聘')");
						sql.append(" and disp_cate2_name  in ('销售','普工/技工','餐饮','人事/行政/后勤','客服','司机/交通服务','物流/仓储','家政保洁/安保','美容/美发','淘宝职位','学生兼职','计算机/互联网/通信','娱乐/休闲','保健按摩','美术/设计/创意','房产中介','超市/百货/零售','教育培训') ");
					}else if("ershouchuangxin".equals(busiName)){
						sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null and business_type ='all'");
					}else if("zhaoshangjiameng".equals(busiName)){
						sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null and business_type ='all'");
					}else if("fangchan".equals(busiName)){
						sql.append(" and disp_cate2_name in ('求租房','二手房','写字楼','商铺租售/生意转让','厂房/仓库/土地/车位','租房','短租房/日租房','合租房') ");
					}
						
					if(!busiName.equals("ershouchuangxin") && !busiName.equals("zhaoshangjiameng")){
						sql.append(" and business_type='"+busiName+"' ");
					}
					sql.append(" and page_type='all' ");
					sql.append(" and disp_city1_name='all'  ");
					sql.append(" group by stat_date,disp_cate2_name ");
				}
				//APP
				sql.append(" union all ");
				sql.append(" select stat_date,disp_cate2_name,sum(pv) as pv ");
				sql.append(" from t_netflow_app_pv_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");
				sql.append(" and pv > 0");
				if("huangye".equals(busiName)){
					sql.append(" and disp_cate2_name in ('搬家','租车','工商注册','投资担保','家装服务','家电维修','保洁清洗','保姆/月嫂','艺术培训','管道疏通','婚庆','餐饮加盟','开锁/换锁/修锁','电脑维修','房屋维修/防水','货运物流','驾校','建材','工装服务','职业技能培训','二手回收') ");
				}else if("zhaopin".equals(busiName)){
					sql.append(" and disp_cate1_name in('全职招聘','兼职招聘')");
					sql.append(" and disp_cate2_name  in ('销售','普工/技工','餐饮','人事/行政/后勤','客服','司机/交通服务','物流/仓储','家政保洁/安保','美容/美发','淘宝职位','学生兼职','计算机/互联网/通信','娱乐/休闲','保健按摩','美术/设计/创意','房产中介','超市/百货/零售','教育培训') ");
				}else if("ershouchuangxin".equals(busiName)){
					sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null and business_type ='all'");
				}else if("zhaoshangjiameng".equals(busiName)){
					sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null and business_type ='all'");
				}else if("fangchan".equals(busiName)){
					sql.append(" and disp_cate2_name in ('求租房','二手房','写字楼','商铺租售/生意转让','厂房/仓库/土地/车位','租房','短租房/日租房','合租房') ");
				}
					
				if(!busiName.equals("ershouchuangxin") && !busiName.equals("zhaoshangjiameng")){
					sql.append(" and business_type='"+busiName+"' ");
				}
				sql.append(" and page_type='all' ");
				sql.append(" and disp_city1_name='all' and disp_cate3_name='all'");
				sql.append(" group by stat_date,disp_cate2_name ");
				if(monthList.size() > 1){
					sql.append(" union all ");
					sql.append(" select stat_date,disp_cate2_name,sum(pv) as pv ");
					sql.append(" from t_netflow_app_pv_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					sql.append(" and pv > 0");
					if("huangye".equals(busiName)){
						sql.append(" and disp_cate2_name in ('搬家','租车','工商注册','投资担保','家装服务','家电维修','保洁清洗','保姆/月嫂','艺术培训','管道疏通','婚庆','餐饮加盟','开锁/换锁/修锁','电脑维修','房屋维修/防水','货运物流','驾校','建材','工装服务','职业技能培训','二手回收') ");
					}else if("zhaopin".equals(busiName)){
						sql.append(" and disp_cate1_name in('全职招聘','兼职招聘')");
						sql.append(" and disp_cate2_name  in ('销售','普工/技工','餐饮','人事/行政/后勤','客服','司机/交通服务','物流/仓储','家政保洁/安保','美容/美发','淘宝职位','学生兼职','计算机/互联网/通信','娱乐/休闲','保健按摩','美术/设计/创意','房产中介','超市/百货/零售','教育培训') ");
					}else if("ershouchuangxin".equals(busiName)){
						sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null and business_type ='all'");
					}else if("zhaoshangjiameng".equals(busiName)){
						sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null and business_type ='all'");
					}else if("fangchan".equals(busiName)){
						sql.append(" and disp_cate2_name in ('求租房','二手房','写字楼','商铺租售/生意转让','厂房/仓库/土地/车位','租房','短租房/日租房','合租房') ");
					}
						
					if(!busiName.equals("ershouchuangxin") && !busiName.equals("zhaoshangjiameng")){
						sql.append(" and business_type='"+busiName+"' ");
					}
					sql.append(" and page_type='all' ");
					sql.append(" and disp_city1_name='all' and disp_cate3_name='all'");
					sql.append(" group by stat_date,disp_cate2_name ");
				}
				sql.append(" )a ");
				sql.append(" group by disp_cate2_name ");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols2 = new ArrayList<String>();
				cols2.add("statDate");
				cols2.add("busiLine");
				cols2.add("type");
				cols2.add("allPv");
				sqlMap.put(sql.toString(), cols2);
			}
			
		}
				 
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("type");
		
		List<BWRLineCityCate> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineCityCate.class, gbList);
		Map<String,List<BWRLineCityCate>>  rsMap = new HashMap<String, List<BWRLineCityCate>>();
		for (BWRLineCityCate vo : rsList) {
			String busiLine = vo.getBusiLine();
			busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			List<BWRLineCityCate> tempList = new ArrayList<BWRLineCityCate>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
			
			//计算 流量变现率 和 客户充足度     
			//流量变现率 =  精准置顶智能现金SUM/城市总PV
			//客户充足度 =  精准置顶智能客户数SUM/城市总PV
			if(vo.getAllPv() == 0 ){
				System.out.println(vo.getType());
				vo.setFluxTranRate(0);
				vo.setCusAdequacy(0);
			}
			else
			{
				vo.setFluxTranRate((double)(vo.getJzCash() + vo.getZdCash() + vo.getZnCash()) / vo.getAllPv()) ;
				vo.setCusAdequacy((double)(vo.getJzCusCount() + vo.getZdCusCount() + vo.getZnCusCount()) / vo.getAllPv()) ;
			}					
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
			
		}
		
		return rsMap;
	
	
	}

	@Override
	public Map<String, List<BWRLineSPIncome>> linePlatformIncomeDatas(
			List<String> dateList) throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		
		List<String> incomTypeList = new ArrayList<String>();
		incomTypeList.add("jingzhun");
		incomTypeList.add("zhiding");
		
		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);
			
			for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
				String busiName = it.next();
				
				if("all".equals(busiName)){ //总体 PASS
					continue;
				}

				for(String incomeType : incomTypeList){
					//分开
					sql.delete(0, sql.length()+1);
					sql.append(" select '" + searchDate + "','"+busiName+"',");
					sql.append(" platform,round(sum(pv)/(count(distinct stat_date)),2) as 'pv', ");
					sql.append(" round(sum(cash)/(count(distinct stat_date)),2) as '收入', ");
					sql.append(" sum(click)/sum(disp) AS 'ctr2' ");//变现率改成了ctr2
					sql.append(" from (");
					sql.append(" select stat_date,platform,sum(pv) as pv , sum(cash/100) as cash,SUM(click) as click ,SUM(disp) as disp ");
					sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + monthList.get(0).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");

					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append(" and platform in(-1,0,2) ");
					sql.append(" group by stat_date,platform  ");
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select stat_date,platform,sum(pv) as pv , sum(cash) as cash ,SUM(click) as click ,SUM(disp) as disp ");
						sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + monthList.get(1).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");

						if("ershouchuangxin".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						sql.append(" and platform in(-1,0,2) ");
						sql.append(" group by stat_date,platform  ");
					}
					sql.append(" ) a");
					sql.append(" GROUP BY platform ");
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("busiLine");
					cols1.add("type");
					
					if("jingzhun".equals(incomeType)){
						cols1.add("jzPv");
						cols1.add("jzCash");
						cols1.add("jzTranRate");
					}else if("zhiding".equals(incomeType)){
						cols1.add("zdPv");
						cols1.add("zdCash");
						cols1.add("zdTranRate");
					}
					
					sqlMap.put(sql.toString(), cols1);
					
					//总计 - 用于计算占比
					sql.delete(0, sql.length()+1);
					sql.append(" select '" + searchDate + "','"+busiName+"',");
					sql.append(" 'all',round(sum(pv)/(count(distinct stat_date)),2) as 'pv', ");
					sql.append(" round(sum(cash)/(count(distinct stat_date)),2) as '收入', ");
					sql.append(" sum(click)/sum(disp) AS 'ctr2' ");
					sql.append(" from (");
					sql.append(" select stat_date,sum(pv) as pv , sum(cash/100) as cash ,SUM(click) as click ,SUM(disp) as disp ");
					sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + monthList.get(0).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");

					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append(" and platform in(-1,0,2) ");
					sql.append(" group by stat_date ");
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select stat_date,sum(pv) as pv , sum(cash) as cash ,SUM(click) as click ,SUM(disp) as disp ");
						sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + monthList.get(1).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");

						if("ershouchuangxin".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						sql.append(" and platform in(-1,0,2) ");
						sql.append(" group by stat_date  ");
					}
					sql.append(" ) a");
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					
					sqlMap.put(sql.toString(), cols1);
					
				}
				
			}
			
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("type");
		
		List<BWRLineSPIncome> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineSPIncome.class, gbList);
		Map<String,List<BWRLineSPIncome>>  rsMap = new HashMap<String, List<BWRLineSPIncome>>();
		
		//总计MAP,不放入返回结果集仅用于计算占比
		Map<String,BWRLineSPIncome> allCountMap = new HashMap<String, BWRLineSPIncome>();
		
		for (BWRLineSPIncome vo : rsList) {
			String busiLine = vo.getBusiLine();
			busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			String platform = vo.getType();
			if("-1".equals(platform)){
				vo.setType("PC");
			}else if("0".equals(platform)){
				vo.setType("M");
			}else if("2".equals(platform)){
				vo.setType("APP");
			}else if("all".equals(platform)){//all 总计VO只放入allCountMap
				allCountMap.put(vo.getStatDate()+vo.getBusiLine(), vo);
				continue;
			}
			
			List<BWRLineSPIncome> tempList = new ArrayList<BWRLineSPIncome>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
					
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}
		
		//计算占比
		for(Iterator<String> it = rsMap.keySet().iterator();it.hasNext();){
			String busiLine = it.next();
			List<BWRLineSPIncome> tempList = rsMap.get(busiLine);
			for (BWRLineSPIncome vo : tempList) {
				String key = vo.getStatDate()+vo.getBusiLine();
				if(allCountMap.containsKey(key)){
					BWRLineSPIncome allVo = allCountMap.get(key);
					vo.setJzPvZb((double) vo.getJzPv() / allVo.getJzPv());
					vo.setJzCashZb((double) vo.getJzCash() / allVo.getJzCash());
					
					if(allVo.getZdPv()==0 ){
						vo.setZdPvZb(0);
					}
					else {
						vo.setZdPvZb((double) vo.getZdPv() / allVo.getZdPv());
					}
					
					if(allVo.getZdCash()==0){			
						vo.setZdCashZb(0);
					}
					else 
					{
						vo.setZdCashZb((double) vo.getZdCash() / allVo.getZdCash());
					}

				}
			}
		}
		return rsMap;
	
	
	}

	@Override
	public Map<String, List<BWRLineSPIncome>> lineSourceIncomeDatas(
			List<String> dateList) throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		
		List<String> incomTypeList = new ArrayList<String>();
		incomTypeList.add("jingzhun");
		incomTypeList.add("zhiding");
		
		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);
			
			for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
				String busiName = it.next();
				
				if("all".equals(busiName)){ //总体 PASS
					continue;
				}			
				
				for(String incomeType : incomTypeList){
					//站内
					sql.delete(0, sql.length()+1);
					sql.append(" select '" + searchDate + "','"+busiName+"',");
					sql.append(" 'zhannei',round(sum(pv)/(count(distinct stat_date)),2) as 'pv', ");
					sql.append(" round(sum(cash)/(count(distinct stat_date)),2) as '收入', ");
					sql.append(" sum(click)/sum(disp) AS 'ctr2' ");
					sql.append(" from (");
					sql.append(" select stat_date,source1,sum(pv) as pv , sum(cash/100) as cash  ,SUM(click) as click ,SUM(disp) as disp ");
					sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + monthList.get(0).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");

					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append(" and  source1 in('站内','直接访问')   ");
					sql.append(" group by stat_date  ");
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select stat_date,source1,sum(pv) as pv , sum(cash/100) as cash ,SUM(click) as click ,SUM(disp) as disp ");
						sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + monthList.get(1).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");

						if("ershouchuangxin".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						sql.append(" and source1 in('站内','直接访问')  ");
						sql.append(" group by stat_date  ");
					}
					sql.append(" ) a");
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("busiLine");
					cols1.add("type");
					
					if("jingzhun".equals(incomeType)){
						cols1.add("jzPv");
						cols1.add("jzCash");
						cols1.add("jzTranRate");
					}else if("zhiding".equals(incomeType)){
						cols1.add("zdPv");
						cols1.add("zdCash");
						cols1.add("zdTranRate");
					}
					
					sqlMap.put(sql.toString(), cols1);
					
					
					//站外付费
					sql.delete(0, sql.length()+1);
					sql.append(" select '" + searchDate + "','"+busiName+"',");
					sql.append(" 'zwff',round(sum(pv)/(count(distinct stat_date)),2) as 'pv', ");
					sql.append(" round(sum(cash)/(count(distinct stat_date)),2) as '收入', ");
					sql.append(" sum(click)/sum(disp) AS 'ctr2' ");
					sql.append(" from (");
					sql.append(" select stat_date,sum(pv) as pv , sum(cash/100) as cash,SUM(click) as click ,SUM(disp) as disp ");
					sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + monthList.get(0).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");

					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append(" and  source1 in('付费浏览器','广告系列','58联盟','付费其他','SEM')   ");
					sql.append(" group by stat_date  ");
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select stat_date,sum(pv) as pv , sum(cash/100) as cash ,SUM(click) as click ,SUM(disp) as disp ");
						sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + monthList.get(1).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");

						if("ershouchuangxin".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						sql.append(" and  source1 in('付费浏览器','广告系列','58联盟','付费其他','SEM')   ");
						sql.append(" group by stat_date  ");
					}
					sql.append(" ) a");
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					
					sqlMap.put(sql.toString(), cols1);
					
					//站外其他
					sql.delete(0, sql.length()+1);
					sql.append(" select '" + searchDate + "','"+busiName+"',");
					sql.append(" 'zwqt',round(sum(pv)/(count(distinct stat_date)),2) as 'pv', ");
					sql.append(" round(sum(cash)/(count(distinct stat_date)),2) as '收入', ");
					sql.append(" sum(click)/sum(disp) AS 'ctr2' ");
					sql.append(" from (");
					sql.append(" select stat_date,sum(pv) as pv , sum(cash/100) as cash,SUM(click) as click ,SUM(disp) as disp ");
					sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + monthList.get(0).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append(" and  source1 in('站外其他','SEO','站外引荐','sou','other','其他')   ");
					sql.append(" group by stat_date  ");
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select stat_date,sum(pv) as pv , sum(cash/100) as cash,SUM(click) as click ,SUM(disp) as disp ");
						sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + monthList.get(1).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");
						
						if("ershouchuangxin".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						sql.append(" and  source1 in('站外其他','SEO','站外引荐','sou','other','其他')   ");
						sql.append(" group by stat_date  ");
					}
					sql.append(" ) a");
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					
					sqlMap.put(sql.toString(), cols1);
					
					
					//总计 - 用于计算占比
					sql.delete(0, sql.length()+1);
					sql.append(" select '" + searchDate + "','"+busiName+"',");
					sql.append(" 'all',round(sum(pv)/(count(distinct stat_date)),2) as 'pv', ");
					sql.append(" round(sum(cash)/(count(distinct stat_date)),2) as '收入', ");
					sql.append(" sum(click)/sum(disp) AS 'ctr2' ");
					sql.append(" from (");
					sql.append(" select stat_date,sum(pv) as pv , sum(cash/100) as cash ,SUM(click) as click ,SUM(disp) as disp ");
					sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + monthList.get(0).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					sql.append(" and  source1 in('站外其他','SEO','站外引荐','sou','other','直接访问','其他','站内','付费浏览器','广告系列','58联盟','付费其他','SEM')   ");
					sql.append(" group by stat_date  ");
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select stat_date,sum(pv) as pv , sum(cash/100) as cash,SUM(click) as click ,SUM(disp) as disp ");
						sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + monthList.get(1).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");
						if("ershouchuangxin".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						sql.append(" and  source1 in('站外其他','SEO','站外引荐','sou','other','直接访问','其他','站内','付费浏览器','广告系列','58联盟','付费其他','SEM')   ");
						sql.append(" group by stat_date  ");
					}
					sql.append(" ) a");
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					
					sqlMap.put(sql.toString(), cols1);
					
				}
				
			}
			
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("type");
		
		List<BWRLineSPIncome> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineSPIncome.class, gbList);
		Map<String,List<BWRLineSPIncome>>  rsMap = new HashMap<String, List<BWRLineSPIncome>>();
		
		//总计MAP,不放入返回结果集仅用于计算占比
		Map<String,BWRLineSPIncome> allCountMap = new HashMap<String, BWRLineSPIncome>();
		
		for (BWRLineSPIncome vo : rsList) {
			String busiLine = vo.getBusiLine();
			busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			String source = vo.getType();
			if("all".equals(source)){//all 总计VO只放入allCountMap
				allCountMap.put(vo.getStatDate()+vo.getBusiLine(), vo);
				continue;
			}else if("zwff".equals(source)){
				vo.setType("站外付费");
			}else if("zwqt".equals(source)){
				vo.setType("站外其他");
			}else if("zhannei".equals(source)){
				vo.setType("站内");
			}
			
			List<BWRLineSPIncome> tempList = new ArrayList<BWRLineSPIncome>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
					
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}
		
		//计算占比
		for(Iterator<String> it = rsMap.keySet().iterator();it.hasNext();){
			String busiLine = it.next();
			List<BWRLineSPIncome> tempList = rsMap.get(busiLine);
			for (BWRLineSPIncome vo : tempList) {
				String key = vo.getStatDate()+vo.getBusiLine();
				if(allCountMap.containsKey(key)){
					BWRLineSPIncome allVo = allCountMap.get(key);
					vo.setJzPvZb((double) vo.getJzPv() / allVo.getJzPv());
					vo.setJzCashZb((double) vo.getJzCash() / allVo.getJzCash());
				
					if(allVo.getZdPv()==0 ){
						vo.setZdPvZb(0);
					}
					else {
						vo.setZdPvZb((double) vo.getZdPv() / allVo.getZdPv());
					}
					
					if(allVo.getZdCash()==0){			
						vo.setZdCashZb(0);
					}
					else 
					{
						vo.setZdCashZb((double) vo.getZdCash() / allVo.getZdCash());
					}

				}
			}
		}
		return rsMap;
	
	
	}

	@Override
	public Map<String, List<BWRLineCusTran>> BWRLineCusTranDatas(
			List<String> dateList) throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		
		List<String> incomTypeList = new ArrayList<String>();
		incomTypeList.add("jingzhun");
		incomTypeList.add("zhiding");
		incomTypeList.add("zhineng");
		
		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);
			
			for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
				String busiName = it.next();
				
				if("all".equals(busiName)){ //总体 PASS
					continue;
				}
	
				for(String incomeType : incomTypeList){
						
					/**展现客户数 消耗客户数 消耗ARPU*/
					sql.delete(0, sql.length()+1);
					sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType+"',");
					sql.append(" count(distinct case when disp>0 then user_id end) as '有展现客户数', ");
					sql.append(" count(distinct case when cost>0 then user_id end) as '有消耗客户数', ");
					sql.append(" sum(cost)/count(distinct case when cost>0 then user_id end)/100 as '有消耗客户ARPU' ");
					sql.append(" from (");
					sql.append(" select user_id,disp,cost  ");
					sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					
					if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select user_id,disp,cost  ");
						sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");
						
						if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}		
						
					}
					sql.append(" ) a");
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("busiLine");
					cols1.add("incomeType");
					cols1.add("showCus");
					cols1.add("costCus");
					cols1.add("costARPU");
					
					sqlMap.put(sql.toString(), cols1);
					
					
					/**新增客户数及ARPU*/
					sql.delete(0, sql.length()+1);
					sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType+"',");
					sql.append(" count(distinct a.user_id),");
					sql.append(" sum(a.xf)/count(distinct a.user_id)/100 as 'ARPU' ");
					sql.append(" from ");
					sql.append(" ( ");
					//本周消费客户
					sql.append("   select user_id,sum(cost) as xf ");
					sql.append("   from (");
					sql.append("	select user_id,cost ");
					sql.append(" 	from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
					sql.append(" 	where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" 	and stat_date <= '"+searchDate+ "' ");
					
					if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append("    and cost>0  ");
					if(monthList.size() > 1){
						sql.append(" 	UNION all  ");
						sql.append("	select user_id,cost ");
						sql.append(" 	from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
						sql.append(" 	where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" 	and stat_date <= '"+searchDate+ "' ");
						
						if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						sql.append("    and cost>0  ");
					}
					sql.append("    ) c");
					sql.append(" group by user_id ");
					sql.append(" )a ");
					sql.append(" left join  ");
					sql.append(" ( ");
					
					//上周消费客户
					
					//需另外计算上周的月跨度
					String lastWeekDate = DateUtil.getStringDate(DateUtil.getDateBefore(DateUtil.strToDate(searchDate, "yyyy-MM-dd"),7), "yyyy-MM-dd"); 
					List<String> lastMonthList = checkTwoMonth(lastWeekDate);
					
					sql.append(" select user_id  ");
					sql.append(" from  ");
					sql.append(" ( ");
					sql.append(" 	select user_id ");
					sql.append(" 	from t_"+incomeType+"_user_stat_new_simplify_" + lastMonthList.get(0).split(";")[0]);
					sql.append(" 	where stat_date >= date_sub('"+lastWeekDate+"',interval 6 day)  ");
					sql.append(" 	and stat_date <= '"+lastWeekDate+ "' ");
					
					if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					
					sql.append("	and cost>0 ");
					
					if(lastMonthList.size() > 1){
						sql.append(" 	UNION all  ");
						sql.append(" 	select user_id ");
						sql.append(" 	from t_"+incomeType+"_user_stat_new_simplify_" + lastMonthList.get(1).split(";")[0]);
						sql.append(" 	where stat_date >= date_sub('"+lastWeekDate+"',interval 6 day)  ");
						sql.append(" 	and stat_date <= '"+lastWeekDate+ "' ");
						
						if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						sql.append("	and cost>0 ");
					}
					
					sql.append("	)d");
					sql.append(" group by user_id ");
					sql.append(" )b ");
					sql.append(" on a.user_id=b.user_id ");
					sql.append(" where b.user_id is null ");
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					List<String> cols2 = new ArrayList<String>();
					cols2.add("statDate");
					cols2.add("busiLine");
					cols2.add("incomeType");
					cols2.add("addCus");
					cols2.add("addARPU");
					
					sqlMap.put(sql.toString(), cols2);
					
					/*流失客户数及ARPU*/
					sql.delete(0, sql.length()+1);
					sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType+"',");
					sql.append(" count(distinct a.user_id),");
					sql.append(" sum(a.xf)/count(distinct a.user_id)/100 as 'ARPU' ");
					sql.append(" from ");
					sql.append(" ( ");
					//上周消费客户
					sql.append("   select user_id,sum(cost) as xf ");
					sql.append("   from (");
					sql.append("	select user_id,cost ");
					sql.append(" 	from t_"+incomeType+"_user_stat_new_simplify_" + lastMonthList.get(0).split(";")[0]);
					sql.append(" 	where stat_date >= date_sub('"+lastWeekDate+"',interval 6 day)  ");
					sql.append(" 	and stat_date <= '"+lastWeekDate+ "' ");
					
					if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append("    and cost>0  ");
					if(lastMonthList.size() > 1){
						sql.append(" 	UNION all  ");
						sql.append("	select user_id,cost ");
						sql.append(" 	from t_"+incomeType+"_user_stat_new_simplify_" + lastMonthList.get(1).split(";")[0]);
						sql.append(" 	where stat_date >= date_sub('"+lastWeekDate+"',interval 6 day)  ");
						sql.append(" 	and stat_date <= '"+lastWeekDate+ "' ");
						
						if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						sql.append("    and cost>0  ");
					}
					sql.append("    ) c");
					sql.append(" group by user_id ");
					sql.append(" )a ");
					sql.append(" left join  ");
					sql.append(" ( ");
					
					//本周消费客户
					sql.append(" select user_id  ");
					sql.append(" from  ");
					sql.append(" ( ");
					sql.append(" 	select user_id ");
					sql.append(" 	from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
					sql.append(" 	where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" 	and stat_date <= '"+searchDate+ "' ");
					
					if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append("	and cost>0 ");
					
					if(monthList.size() > 1){
						sql.append(" 	UNION all  ");
						sql.append(" 	select user_id ");
						sql.append(" 	from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
						sql.append(" 	where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" 	and stat_date <= '"+searchDate+ "' ");
						
						if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						sql.append("	and cost>0 ");
					}
					
					sql.append("	)d");
					sql.append(" group by user_id ");
					sql.append(" )b ");
					sql.append(" on a.user_id=b.user_id ");
					sql.append(" where b.user_id is null ");
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					List<String> cols3 = new ArrayList<String>();
					cols3.add("statDate");
					cols3.add("busiLine");
					cols3.add("incomeType");
					cols3.add("lostCus");
					cols3.add("lostARPU");
					
					sqlMap.put(sql.toString(), cols3);
					
					/** 处理 TO精准  &&  TO置顶 
					 *  精准TO精准= 上周做精准，本周继续做精准/上周精准消费客户数
					 *  精准TO置顶= 上周做精准，本周只做置顶/上周精准消费客户数
					 *  其他2个以此类推
					 */
					
					if("zhineng".equals(incomeType)){
						continue;
					}
					
					/** 上周消费客户数 */
					sql.delete(0, sql.length()+1);
					sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType+"',");
					sql.append(" count(distinct case when cost>0 then user_id end) as '上周消费客户数' ");
					sql.append(" from (");
					sql.append(" select user_id,cost  ");
					sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + lastMonthList.get(0).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+lastWeekDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+lastWeekDate+ "' ");
				
					if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					if(lastMonthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select user_id,cost  ");
						sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + lastMonthList.get(1).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+lastWeekDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+lastWeekDate+ "' ");
						
						if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
					}
					sql.append(" ) a");
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					List<String> cols4 = new ArrayList<String>();
					cols4.add("statDate");
					cols4.add("busiLine");
					cols4.add("incomeType");
					cols4.add("lastWeekCostCus");
					
					sqlMap.put(sql.toString(), cols4);
					
					
					/** 自己 TO 自己  = like  精准 TO 精准   、 置顶 TO 置顶 */
					
					sql.delete(0, sql.length()+1);
					sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType+"',");
					sql.append(" count(distinct a.user_id) ");
					sql.append(" from (");
					sql.append("	select distinct user_id from  ");
					sql.append("	(");
					sql.append("		select user_id ");
					sql.append(" 		from t_"+incomeType+"_user_stat_new_simplify_" + lastMonthList.get(0).split(";")[0]);
					sql.append(" 		where stat_date >= date_sub('"+lastWeekDate+"',interval 6 day)  ");
					sql.append(" 		and stat_date <= '"+lastWeekDate+ "' ");
									
					if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append("		and cost>0");
					if(lastMonthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append("		select user_id ");
						sql.append(" 		from t_"+incomeType+"_user_stat_new_simplify_" + lastMonthList.get(1).split(";")[0]);
						sql.append(" 		where stat_date >= date_sub('"+lastWeekDate+"',interval 6 day)  ");
						sql.append(" 		and stat_date <= '"+lastWeekDate+ "' ");
						
						if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						sql.append("		and cost>0");
					}
					sql.append("	)c ");
					sql.append(" )a ");
					sql.append(" JOIN ");
					sql.append(" (");
					sql.append("	select distinct user_id from  ");
					sql.append("	( ");
					sql.append("		select user_id  ");
					sql.append(" 		from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
					sql.append(" 		where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" 		and stat_date <= '"+searchDate+ "' ");			
					
					if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append("		and cost>0  ");
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append("		select user_id  ");
						sql.append(" 		from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
						sql.append(" 		where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" 		and stat_date <= '"+searchDate+ "' ");
											
						if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						sql.append("		and cost>0  ");
						
					}
					sql.append("	)d ");
					sql.append(")b ");
					sql.append("ON a.user_id=b.user_id ");
					
					
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					List<String> cols5 = new ArrayList<String>();
					cols5.add("statDate");
					cols5.add("busiLine");
					cols5.add("incomeType");
					if("jingzhun".equals(incomeType)){
						cols5.add("toJZ");
					}else if("zhiding".equals(incomeType)){
						cols5.add("toZD");
					}
					
					sqlMap.put(sql.toString(), cols5);
					
					
					
					/** 精准 TO 置顶 、 置顶 TO 精准 */
					
					sql.delete(0, sql.length()+1);
					sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType+"',");
					sql.append(" count(distinct a.user_id)  ");
					sql.append(" FROM ");
					sql.append(" ( ");
					sql.append("	select distinct user_id from  ");
					sql.append("	( ");
					sql.append("		select user_id");
					sql.append(" 		from t_"+incomeType+"_user_stat_new_simplify_" + lastMonthList.get(0).split(";")[0]);
					sql.append(" 		where stat_date >= date_sub('"+lastWeekDate+"',interval 6 day)  ");
					sql.append(" 		and stat_date <= '"+lastWeekDate+ "' ");
					
					if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append("		and cost>0 ");
					if(lastMonthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append("		select user_id");
						sql.append(" 		from t_"+incomeType+"_user_stat_new_simplify_" + lastMonthList.get(1).split(";")[0]);
						sql.append(" 		where stat_date >= date_sub('"+lastWeekDate+"',interval 6 day)  ");
						sql.append(" 		and stat_date <= '"+lastWeekDate+ "' ");
						
						if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						sql.append("		and cost>0 ");
					}
					
					
					sql.append("	)d ");
					sql.append(" )a");
					sql.append(" JOIN ");
					sql.append(" ( ");
					sql.append("	select distinct user_id from  ");
					sql.append("	( ");
					sql.append("		select user_id ");
					if("jingzhun".equals(incomeType)){
						sql.append(" 		from t_zhiding_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
					}else if("zhiding".equals(incomeType)){
						sql.append(" 		from t_jingzhun_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
					}
					sql.append(" 		where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" 		and stat_date <= '"+searchDate+ "' ");
					
					if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append("		and cost>0 ");
					if(monthList.size() > 1){
						sql.append("  UNION all  ");
						sql.append("		select user_id ");
						if("jingzhun".equals(incomeType)){
							sql.append(" 		from t_zhiding_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
						}else if("zhiding".equals(incomeType)){
							sql.append(" 		from t_jingzhun_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
						}
						sql.append(" 		where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" 		and stat_date <= '"+searchDate+ "' ");
						
						if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						sql.append("		and cost>0 ");
					}
					
					sql.append("	)e ");
					sql.append(" )b ");
					sql.append(" on a.user_id=b.user_id ");
					sql.append(" LEFT JOIN ");
					sql.append(" (");
					sql.append("	select distinct user_id from  ");
					sql.append("	( ");
					sql.append("		select user_id ");
					sql.append(" 		from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
					sql.append(" 		where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" 		and stat_date <= '"+searchDate+ "' ");
					
					if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					
					sql.append("		and cost>0 ");
					if(monthList.size() > 1){
						sql.append("  UNION all  ");
						sql.append("		select user_id ");
						sql.append(" 		from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
						sql.append(" 		where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" 		and stat_date <= '"+searchDate+ "' ");
						
						if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						
						sql.append("		and cost>0 ");
					}
					sql.append("	)f ");
					sql.append(" )c");
					sql.append(" on a.user_id=c.user_id ");
					sql.append(" where c.user_id is null; ");
					
					
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					List<String> cols6 = new ArrayList<String>();
					cols6.add("statDate");
					cols6.add("busiLine");
					cols6.add("incomeType");
					if("jingzhun".equals(incomeType)){
						cols6.add("toZD");
					}else if("zhiding".equals(incomeType)){
						cols6.add("toJZ");
					}
					
					sqlMap.put(sql.toString(), cols6);
				}
				
			}
			
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("incomeType");
		
		List<BWRLineCusTran> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineCusTran.class, gbList);
		Map<String,List<BWRLineCusTran>>  rsMap = new HashMap<String, List<BWRLineCusTran>>();
		
		for (BWRLineCusTran vo : rsList) {
			String busiLine = vo.getBusiLine();
			busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			
			//计算TO精准  TO置顶的占比 和流失占比
			String incomeType = vo.getIncomeType();
			if("jingzhun".equals(incomeType)){
				vo.setToJZ((double)vo.getToJZ() / vo.getLastWeekCostCus());
				vo.setToZD((double)vo.getToZD() / vo.getLastWeekCostCus());
				vo.setLostZB((double) (1- vo.getToJZ() - vo.getToZD()));
				vo.setIncomeType("精准");
			}else if("zhiding".equals(incomeType)){
				vo.setToJZ((double)vo.getToJZ() / vo.getLastWeekCostCus());
				vo.setToZD((double)vo.getToZD() / vo.getLastWeekCostCus());
				vo.setLostZB((double) (1- vo.getToJZ() - vo.getToZD()));
				vo.setIncomeType("置顶");
			}else if("zhineng".equals(incomeType)){
				vo.setIncomeType("智能");
			}
			
			List<BWRLineCusTran> tempList = new ArrayList<BWRLineCusTran>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
			
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}
		
		return rsMap;
	}

	@Override
	public Map<String, List<BWRLineAccount>> BWRLineAccountDatas(
			List<String> dateList) throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		
		
		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);
			
			for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
				String busiName = it.next();
				
				
				if("all".equals(busiName)){ //总体 PASS
					continue;
				}
				
				String conditions = busiLineConsMap.get(busiName).replace("disp_cate1_name", "source_cate1_name").replace("disp_cate2_name", "source_cate2_name");
				
				//需另外计算上周的月跨度
				String lastWeekDate = DateUtil.getStringDate(DateUtil.getDateBefore(DateUtil.strToDate(searchDate, "yyyy-MM-dd"),7), "yyyy-MM-dd"); 
				List<String> lastMonthList = checkTwoMonth(lastWeekDate);


				//余额 
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" sum(case when a.stat_date='"+searchDate+"' then a.ye end) as '新进余额', ");
				sql.append(" count(distinct a.user_id) as '新进余额客户', ");
				sql.append(" sum(case when a.stat_date='"+searchDate+"' then a.ye end)/count(distinct a.user_id)  ");
				sql.append(" from (");
				sql.append(" select user_id,stat_date,sum(adcash_balance+adgift_balance)/100 as ye  ");
				sql.append(" from t_common_user_account_new_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");
				sql.append(" and is_vip=1 ");
				sql.append(" and (adcash_balance + adgift_balance) >0 ");
				sql.append(" and " + conditions);
				sql.append(" group by user_id ");
				
				if(monthList.size() > 1){
					sql.append(" UNION all  ");
					sql.append(" select user_id,stat_date,sum(adcash_balance+adgift_balance)/100 as ye  ");
					sql.append(" from t_common_user_account_new_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					sql.append(" and is_vip=1 ");
					sql.append(" and (adcash_balance + adgift_balance) >0 ");
					sql.append(" and " + conditions);
					sql.append(" group by user_id ");
				}
				sql.append(" ) a");
				
				sql.append(" LEFT JOIN( ");
				sql.append(" select user_id,stat_date,sum(adcash_balance+adgift_balance)/100 as ye ");
				sql.append(" from t_common_user_account_new_" + lastMonthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+lastWeekDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+lastWeekDate+ "' ");
				sql.append(" and is_vip=1 ");
				sql.append(" and adcash_balance + adgift_balance>0 ");
				sql.append(" and " + conditions);
				sql.append(" group by user_id ");
				if(lastMonthList.size() > 1){
					sql.append(" UNION all  ");
					sql.append(" select user_id,stat_date,sum(adcash_balance+adgift_balance)/100 as ye ");
					sql.append(" from t_common_user_account_new_" + lastMonthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+lastWeekDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+lastWeekDate+ "' ");
					sql.append(" and is_vip=1 ");
					sql.append(" and adcash_balance + adgift_balance>0 ");
					sql.append(" and " + conditions);
					sql.append(" group by user_id ");
				}
				sql.append(" )b ");
				sql.append(" on a.user_id=b.user_id ");
				sql.append(" where b.user_id is null;");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("busiLine");
				cols1.add("newBlance");
				cols1.add("newBlanceCus");
				cols1.add("newAvgBlance");
				
				sqlMap.put(sql.toString(), cols1);
				
				//充值 
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" sum(a.cz)/count(distinct stat_date) as '总充值', ");
				sql.append(" sum(a.kh)/count(distinct stat_date) as '充值客户',  ");
				sql.append(" sum(a.cz)/sum(a.kh)  ");
				sql.append(" from (");
				sql.append(" select stat_date,sum(adcash_deposit+adgift_deposit)/100 as cz,count(distinct user_id) as kh  ");
				sql.append(" from t_common_user_account_new_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");
				sql.append(" and is_vip=1 ");
				sql.append(" and adcash_deposit+adgift_deposit>0 ");
				sql.append(" and " + conditions);
				sql.append(" group by stat_date ");
				
				if(monthList.size() > 1){
					sql.append(" UNION all  ");
					sql.append(" select stat_date,sum(adcash_deposit+adgift_deposit)/100 as cz,count(distinct user_id) as kh  ");
					sql.append(" from t_common_user_account_new_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					sql.append(" and is_vip=1 ");
					sql.append(" and adcash_deposit+adgift_deposit>0 ");
					sql.append(" and " + conditions);
					sql.append(" group by stat_date ");
				}
				sql.append(" ) a");
				
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols2 = new ArrayList<String>();
				cols2.add("statDate");
				cols2.add("busiLine");
				cols2.add("totalDeposit");
				cols2.add("depositCus");
				cols2.add("AvgDeposit");
				
				sqlMap.put(sql.toString(), cols2);
				
				
				//消耗 
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" sum(a.cz)/count(distinct stat_date) as '总消耗', ");
				sql.append(" sum(a.kh)/count(distinct stat_date) as '消耗客户',  ");
				sql.append(" sum(a.cz)/sum(a.kh)   ");
				sql.append(" from (");
				sql.append(" select stat_date,sum(adcash_consume+adgift_consume)/100 as cz,count(distinct user_id) as kh  ");
				sql.append(" from t_common_user_account_new_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");
				sql.append(" and is_vip=1 ");
				sql.append(" and adcash_consume+adgift_consume>0 ");
				sql.append(" and " + conditions);
				sql.append(" group by stat_date ");
				if(monthList.size() > 1){
					sql.append(" UNION all  ");
					sql.append(" select stat_date,sum(adcash_consume+adgift_consume)/100 as cz,count(distinct user_id) as kh  ");
					sql.append(" from t_common_user_account_new_" + monthList.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					sql.append(" and is_vip=1 ");
					sql.append(" and adcash_consume+adgift_consume>0 ");
					sql.append(" and " + conditions);
					sql.append(" group by stat_date ");
				}
				sql.append(" ) a");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols3 = new ArrayList<String>();
				cols3.add("statDate");
				cols3.add("busiLine");
				cols3.add("totalCost");
				cols3.add("costCus");
				cols3.add("avgCost");
				
				sqlMap.put(sql.toString(), cols3);
				
			}
			
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		
		List<BWRLineAccount> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineAccount.class, gbList);
		Map<String,List<BWRLineAccount>>  rsMap = new HashMap<String, List<BWRLineAccount>>();
		
		for (BWRLineAccount vo : rsList) {
			String busiLine = vo.getBusiLine();
			busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			
			List<BWRLineAccount> tempList = new ArrayList<BWRLineAccount>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
			
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}
		
		return rsMap;
	}


	@Override
	public Map<String, List<String>> cityProbDatas(List<String> dateList,String incomeType)
			throws Exception {

		HashMap<String, String> busLineConditionMap = new HashMap<String, String>();
		
		if(busLineConditionMap.isEmpty()){
			busLineConditionMap.put("huangye", "'搬家','租车','工商注册','投资担保','家装服务','家电维修','保洁清洗','保姆/月嫂','家装服务','家电维修','艺术培训','管道疏通','婚庆','餐饮加盟','开锁/换锁/修锁','电脑维修','房屋维修/防水','过户/验车','货运物流','摄影摄像','驾校'");
			//busLineConditionMap.put("ershou", "'二手手机','二手回收','宠物狗','二手家具','二手摩托车','台式机/配件','笔记本电脑','自行车/电动车','手机号码','办公用品/设备','二手设备','二手家电','服装/鞋帽/箱包','文体/户外/乐器','数码产品','平板电脑','艺术品/收藏品','宠物店/服务'");
			busLineConditionMap.put("zhaopin", "'销售','普工/技工','超市/百货/零售','餐饮','人事/行政/后勤','客服','司机','物流/仓储','家政保洁/安保','美容/美发','娱乐/休闲','保健按摩','计算机/互联网/通信','房产中介','淘宝职位','美术/设计/创意','服务员','教育培训','礼仪/模特' ");
			busLineConditionMap.put("fangchan", "'二手房','租房','写字楼','商铺租售/生意转让','厂房/仓库/土地/车位','合租房','短租房/日租房' ");
			busLineConditionMap.put("ershouchuangxin", "'二手求购','宠物赠送/领养','办公用品/设备','宠物用品/食品','宠物猫','二手设备','花鸟鱼虫','宠物狗','手机号码','成人用品','艺术品/收藏品','通讯业务','二手回收','宠物店/服务','校园二手' ");		
			busLineConditionMap.put("zhaoshangjiameng", "'美容保健加盟','礼品/小商品加盟','餐饮加盟','服装鞋包','建材加盟','机械加盟','网络服务加盟','生活服务加盟','教育/母婴加盟','家居环保加盟','汽车服务加盟','农业加盟','特色加盟' ");
			
			}
		
		HashMap<String, String> thresholdMap = new HashMap<String, String>();
		
		if(thresholdMap.isEmpty()){
			thresholdMap.put("huangye,jingzhun", "10000");
			thresholdMap.put("ershouchuangxin,jingzhun", "50");
			thresholdMap.put("zhaoshangjiameng,jingzhun", "50");
			thresholdMap.put("fangchan,jingzhun", "5000");
			thresholdMap.put("zhaopin,jingzhun", "10000");
			
			thresholdMap.put("huangye,zhiding", "5000");
			thresholdMap.put("ershouchuangxin,zhiding", "50");
			thresholdMap.put("fangchan,zhiding", "2000");
			thresholdMap.put("zhaopin,zhiding", "2000");
			thresholdMap.put("zhaoshangjiameng,zhiding", "50");
		}
				
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		
		String searchDate=dateList.get(0);
		String lastweekDate=dateList.get(2);
		
		//跨月检查  
		List<String> monthList = checkTwoMonth(searchDate);  //本周
		List<String> monthList1 = checkTwoMonth(lastweekDate);   //上周
		
		Map<String,List<String>> rMap = new HashMap<String,List<String>>();
			
		for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
			String busiName = it.next();
				
			Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
			Map<String,List<String>> sqlMap2 = new HashMap<String,List<String>>();
			StringBuffer sql = new StringBuffer();
			
			if("all".equals(busiName)||"ershouche".equals(busiName)){ //总体不看 和二手车
				continue;
			}

			if("zhiding".equals(incomeType) && busiName.equals("zhaoshangjiameng")){//招商加盟 置顶的没数
				continue;
			}
				
			//精准 置顶收入的环比下降在前五的城市
			
			sql.delete(0, sql.length()+1);
			sql.append(" select '" +busiName+"',");
			sql.append(" a.disp_city1_name as 'city',avg(a.jingzhun)*0.01,avg(a.jingzhun)/avg(b.jingzhun)-1 as 'zhoubi' ");
			sql.append(" from (");
				sql.append(" (select stat_date, disp_city1_name, sum(cash) as jingzhun  ");
				sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");
				if("ershouchuangxin".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) );
				}else if("zhaoshangjiameng".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) );
				}else{
						sql.append(" and business_type_biz ='" + busiName +"' ");
				}
				sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");
				sql.append(" group by 1,2 ");
				
				if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" (select stat_date, disp_city1_name, sum(cash) as jingzhun  ");
						sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");
						if("ershouchuangxin".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");
						sql.append(" group by 1,2) ");
					}
			
				sql.append(" )a ");
				sql.append("left join ");
				sql.append(" (select stat_date, disp_city1_name, sum(cash) as jingzhun  ");
				sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList1.get(0).split(";")[0]);
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 13 day)  ");
				sql.append(" and stat_date <= date_sub('"+searchDate+ "' ,interval 7 day) ");
				if("ershouchuangxin".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) );
				}else if("zhaoshangjiameng".equals(busiName)){							
					sql.append(" and " + busiLineConsMap.get(busiName) );
				}else{
						sql.append(" and business_type_biz ='" + busiName +"' ");
				}
				sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");
				sql.append(" group by 1,2 ");
				
				if(monthList1.size() > 1){
					sql.append(" UNION all  ");
					sql.append(" (select stat_date, disp_city1_name, sum(cash) as jingzhun  ");
					sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList1.get(1).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 13 day)  ");
					sql.append(" and stat_date <= date_sub('"+searchDate+ "' ,interval 7 day) ");
					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");
					sql.append(" group by 1,2) ");
				}
						
				sql.append(" )b ");
				sql.append(" on a.disp_city1_name=b.disp_city1_name)");				
				sql.append(" GROUP BY city");
				sql.append(" order by zhoubi");
				sql.append(" limit 5");
					
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols1 = new ArrayList<String>();
	
				cols1.add("busiLine");
				cols1.add("type");//其实是城市			
				cols1.add("jzCash");							
				cols1.add("fluxTranRate");//其实是环比				
				sqlMap.put(sql.toString(), cols1);	
				
				List<String> gbList = new ArrayList<String>();
				gbList.add("busiLine");
				gbList.add("type");					
				List<BWRLineCityCate> cityList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineCityCate.class, gbList);
				cityList=sortUtil.sortListByFileNameVo(cityList,BWRLineCityCate.class,"fluxTranRate",1);
					
				//获取5个城市名
				List<String> citys = new ArrayList<String>();
				for (BWRLineCityCate vo : cityList) {				
					citys.add(vo.getType());			
				}
					
				//对每个城市查5个类别
				for (String city : citys)
				{
	
					sql.delete(0, sql.length()+1);
					sql.append(" select case when a.disp_city1_name = '"+city+"' then '"+city+"' end, ");
					sql.append(" a.disp_cate2_name as 'cate',avg(a.jingzhun)*0.01,avg(a.jingzhun)/avg(b.jingzhun)-1 as 'zhoubi' ");
					sql.append(" from (");
						sql.append(" (select stat_date, disp_cate2_name, sum(cash) as jingzhun,disp_city1_name ");		
						sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");
						if("ershouchuangxin".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null ");
						}else if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null ");
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						sql.append(" and disp_city1_name = '"+city+"' ");
						if(busLineConditionMap.containsKey(busiName))
						{
							sql.append(" and disp_cate2_name in ("+busLineConditionMap.get(busiName)+")");
						}		
						sql.append(" group by 1,2 ");
					
						sql.append(" having jingzhun> "+thresholdMap.get(busiName+","+incomeType)+" ");			
							
						if(monthList.size() > 1){
							sql.append(" UNION all  ");								
							sql.append(" (select stat_date, disp_cate2_name, sum(cash) as jingzhun,disp_city1_name ");		
							sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
							sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
							sql.append(" and stat_date <= '"+searchDate+ "' ");
							if("ershouchuangxin".equals(busiName)){							
								sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null ");
							}else if("zhaoshangjiameng".equals(busiName)){							
								sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null ");
							}else{
									sql.append(" and business_type_biz ='" + busiName +"' ");
							}
							sql.append(" and disp_city1_name = '"+city+"' ");
						    if(busLineConditionMap.containsKey(busiName))
							{
								sql.append(" and disp_cate2_name in ("+busLineConditionMap.get(busiName)+")");
							}		
							sql.append(" group by 1,2 ");
							
							sql.append(" having jingzhun> "+thresholdMap.get(busiName+","+incomeType)+" )");											
						}
					
						sql.append(" )a");
						sql.append(" left join ");
						sql.append(" (select stat_date, disp_cate2_name, sum(cash) as jingzhun,disp_city1_name ");
						sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList1.get(0).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 13 day)  ");
						sql.append(" and stat_date <= date_sub('"+searchDate+ "' ,interval 7 day) ");
						if("ershouchuangxin".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) + " and disp_cate2_name is not null ");
						}else if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null ");
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						sql.append(" and disp_city1_name = '"+city+"' ");
						if(busLineConditionMap.containsKey(busiName))
						{
							sql.append(" and disp_cate2_name in ("+busLineConditionMap.get(busiName)+")");
						}										
						sql.append(" group by 1,2 ");
						
						if(monthList1.size() > 1){
							sql.append(" UNION all  ");														
							sql.append(" (select stat_date, disp_cate2_name, sum(cash) as jingzhun,disp_city1_name ");
							sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList1.get(1).split(";")[0]);
							sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 13 day)  ");
							sql.append(" and stat_date <= date_sub('"+searchDate+ "' ,interval 7 day) ");
							if("ershouchuangxin".equals(busiName)){							
								sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null ");
							}else if("zhaoshangjiameng".equals(busiName)){							
								sql.append(" and " + busiLineConsMap.get(busiName)  + " and disp_cate2_name is not null ");
							}else{
									sql.append(" and business_type_biz ='" + busiName +"' ");
							}
							sql.append(" and disp_city1_name = '"+city+"' ");
							if(busLineConditionMap.containsKey(busiName))
							{
								sql.append(" and disp_cate2_name in ("+busLineConditionMap.get(busiName)+")");
							}										
							sql.append(" group by 1,2) ");
						}	
						
						sql.append(" )b ");
						sql.append(" on a.disp_cate2_name=b.disp_cate2_name )");
						sql.append(" GROUP BY cate");
						sql.append(" having zhoubi is not null ");
						sql.append(" order by zhoubi");
						sql.append(" limit 5");						
						sql.append(" :"+ DBType.db_94_data_app.getValue());
							
						List<String> cols2 = new ArrayList<String>();			
						cols2.add("statDate"); //其实是城市
						cols2.add("type");				
						cols2.add("jzCash");							
						cols2.add("fluxTranRate");//其实是环比		
						
						sqlMap2.put(sql.toString(), cols2);
					
				}
					
				List<String> gbList1 = new ArrayList<String>();
				gbList1.add("statDate");
				gbList1.add("type");
					
				List<BWRLineCityCate> rsList = comDeal.AssembledDataBySqlMap(sqlMap2, BWRLineCityCate.class, gbList1);				
				rsList=sortUtil.sortListByFileNameVo(rsList,BWRLineCityCate.class,"fluxTranRate",1);
		
			List<String> finalList = new ArrayList<String>();
			
			for (BWRLineCityCate cityvo : cityList) {				
				
				StringBuffer values = new StringBuffer();
							
				String city=cityvo.getType();
				values.append(cityvo.getType()+";");
				values.append(vt.qfw2(cityvo.getJzCash())+";");
				values.append(vt.tranRateTwo(cityvo.getFluxTranRate())+";");
				
				for (BWRLineCityCate vo : rsList) {				
					if(city.equals(vo.getStatDate()))
					{
						values.append(vo.getType()+";");
						values.append(vt.qfw2(vo.getJzCash())+";");
						values.append(vt.tranRateTwo(vo.getFluxTranRate())+";");
					}	
				}
				
				if(values.toString().split(";").length > 1  && values.toString().split(";").length<18){
					while(values.toString().split(";").length < 18){					
						values.append("--;");					
					}
				}
				finalList.add(values.toString());
						
			}
		
			busiName = MyStore.busilineTran(busiName);		
			rMap.put(busiName,finalList);	
			
		}					
		return rMap;
	}
	

	@Override
	public Map<String, List<BWRLineCityCate>> cityCateDatas(List<String> dateList)
			throws Exception {

		
		Map<String,List<String>> sqlMap2 = new HashMap<String,List<String>>();
		
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		
		List<String> incomTypeList = new ArrayList<String>();
		incomTypeList.add("jingzhun");
		incomTypeList.add("zhiding");
		incomTypeList.add("zhineng");
		
		String searchDate=dateList.get(0);
			
		//保存中间结果 少查询100次
		Map<String,List<BWRLineCityCate>>  Top20Map = new HashMap<String, List<BWRLineCityCate>>();
		
		//跨月检查  
		List<String> monthList = checkTwoMonth(searchDate); 
		
		for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
			String busiName = it.next();
					
			Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
				
			if("all".equals(busiName) || "ershouche".equals(busiName) || "fangchan".equals(busiName) ){ //总体 二手车 房产不看
				continue;
			}			
			
			//计算本周的城市类别 top20 ，  依据精准 置顶 智能 收入数据之和
		
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"', CONCAT(a.disp_city1_name,'_',a.disp_cate2_name),");
				if("zhaoshangjiameng".equals(busiName))
				{
					sql.append(" (sum(a.cash)+sum(c.cash))/count(distinct a.stat_date)as '消耗',avg(a.cash),avg(c.cash),avg(a.user_id),avg(c.user_id)");
				}else 
				{
					sql.append(" (sum(a.cash)+sum(b.cash)+sum(c.cash))/count(distinct a.stat_date)as '消耗',avg(a.cash),avg(b.cash),avg(c.cash),avg(a.user_id),avg(b.user_id),avg(c.user_id)");
				}
				
				sql.append(" from (");
						
				for(String incomeType : incomTypeList){
					
					if("zhaoshangjiameng".equals(busiName) && "zhiding".equals(incomeType))
						continue;
					
					sql.append(" select stat_date, disp_city1_name, disp_cate2_name,sum(cash)/100 as cash,count(distinct case when cost>0 then  user_id end) as user_id ");
					sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(0).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");

					if("ershouchuangxin".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else if("zhaoshangjiameng".equals(busiName)){							
						sql.append(" and " + busiLineConsMap.get(busiName) );
					}else{
							sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ")  and disp_cate2_name is not null");
					sql.append(" group by 1,2,3 ");
					
		
					if(monthList.size() > 1){
						
						sql.append(" UNION all  ");
						sql.append(" select stat_date, disp_city1_name, disp_cate2_name,sum(cash)/100 as cash,count(distinct case when cost>0 then  user_id end) as user_id ");
						sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + monthList.get(1).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");

						if("ershouchuangxin".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and " + busiLineConsMap.get(busiName) );
						}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
						}
						sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ")  and disp_cate2_name is not null");
						sql.append(" group by 1,2,3 ");
					}
								
					if("jingzhun".equals(incomeType)){ 
						sql.append(" )a");
						sql.append(" left join (");
					}else if("zhiding".equals(incomeType)){ 
						sql.append(" )b");
						sql.append(" on a.disp_cate2_name=b.disp_cate2_name and a.stat_date=b.stat_date and a.disp_city1_name=b.disp_city1_name");
						sql.append(" left join (");
					}else { 
						sql.append(" )c");
						sql.append(" on a.disp_cate2_name=c.disp_cate2_name and a.stat_date=c.stat_date and a.disp_city1_name=c.disp_city1_name");
					}
					
				}

				sql.append(" GROUP BY 3 ");
				sql.append(" order by 4 DESC ");
				sql.append(" limit 20 ");
					
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("busiLine");
				cols1.add("type");
                cols1.add("allCash");
                
                if("zhaoshangjiameng".equals(busiName))
                {
                	cols1.add("jzCash");
                    cols1.add("znCash");
                    cols1.add("jzCusCount");            
                    cols1.add("znCusCount");
                }else
                {
                	cols1.add("jzCash");
                	cols1.add("zdCash");
                    cols1.add("znCash");
                    cols1.add("jzCusCount");
                    cols1.add("zdCusCount");
                    cols1.add("znCusCount");
                }
                
                sqlMap.put(sql.toString(), cols1);
                
        		List<String> gbList = new ArrayList<String>();
        		gbList.add("statDate");
        		gbList.add("busiLine");
        		gbList.add("type");
        		
        		List<BWRLineCityCate> top20List = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineCityCate.class, gbList);
        		
        		Top20Map.put(busiName,top20List);
        		
        		//根据得出的城市类别  再查本周和上周的数据
        		for(String curDate:dateList)
        		{
        			List<String> curmonthList = checkTwoMonth(curDate);
        			for(BWRLineCityCate vo:top20List)
        			{
        				
        				String citycate=vo.getType();
            			String city =citycate.split("_")[0];
            			String cate =citycate.split("_")[1];
            			
            			//如果是本周就不用查询了
            			if(!curDate.equals(dateList.get(0)))
            			{
	        				//精准 置顶 智能 收入数据
	        				for(String incomeType : incomTypeList){
	        					
	        					if("zhaoshangjiameng".equals(busiName) && "zhiding".equals(incomeType) ){
	        						continue;
	        					}
	        					
	        					sql.delete(0, sql.length()+1);
	        					sql.append(" select '" + curDate + "','"+busiName+"',CONCAT(a.disp_city1_name,'_',a.disp_cate2_name),");
	        					sql.append(" avg(a) as '消耗', avg(u) as '客户数' ");
	        					sql.append(" from (");
	        					sql.append(" select stat_date, disp_city1_name,disp_cate2_name, sum(cash)/100 a,  ");
	        					sql.append(" count(distinct case when cost>0 then  user_id end) u  ");
	        					sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + curmonthList.get(0).split(";")[0]);
	        					sql.append(" where stat_date >= date_sub('"+curDate+"',interval 6 day)  ");
	        					sql.append(" and stat_date <= '"+curDate+ "' ");
	
	        					if("ershouchuangxin".equals(busiName)){							
	        						sql.append(" and " + busiLineConsMap.get(busiName) );
	        					}else if("zhaoshangjiameng".equals(busiName)){							
	        						sql.append(" and " + busiLineConsMap.get(busiName) );
	        					}else{
	        							sql.append(" and business_type_biz ='" + busiName +"' ");
	        					}
	        					
	        					sql.append(" and disp_city1_name ='" + city + "'  and disp_cate2_name ='" + cate + "'");     		
	        					sql.append(" group by 1");
	        					
	        					if(curmonthList.size() > 1){
	        						sql.append(" UNION all  ");
	        						sql.append(" select stat_date, disp_city1_name,disp_cate2_name, sum(cash)/100 a,  ");
	        						sql.append(" count(distinct case when cost>0 then  user_id end) u  ");
	        						sql.append(" from t_"+incomeType+"_user_stat_new_simplify_" + curmonthList.get(1).split(";")[0]);
	        						sql.append(" where stat_date >= date_sub('"+curDate+"',interval 6 day)  ");
	        						sql.append(" and stat_date <= '"+curDate+ "' ");
	
	        						if("ershouchuangxin".equals(busiName)){							
	        							sql.append(" and " + busiLineConsMap.get(busiName) );
	        						}else if("zhaoshangjiameng".equals(busiName)){							
	        							sql.append(" and " + busiLineConsMap.get(busiName) );
	        						}else{
	        								sql.append(" and business_type_biz ='" + busiName +"' ");
	        						}
	        						
	        						sql.append(" and disp_city1_name ='" + city + "'  and disp_cate2_name ='" + cate + "'");
	            					sql.append(" group by 1");
	        					}
	        					sql.append(" ) a");	
	        					
	        					sql.append(" :"+ DBType.db_94_data_app.getValue());
	        					List<String> cols2 = new ArrayList<String>();
	        					cols2.add("statDate");
	        					cols2.add("busiLine");
	        					cols2.add("type");
	        					
	        					if("jingzhun".equals(incomeType)){
	        						cols2.add("jzCash");
	        						cols2.add("jzCusCount");
	        					}else if("zhiding".equals(incomeType)){
	        						cols2.add("zdCash");
	        						cols2.add("zdCusCount");
	        					}else if("zhineng".equals(incomeType)){
	        						cols2.add("znCash");
	        						cols2.add("znCusCount");
	        					}
	        					
	        					sqlMap2.put(sql.toString(), cols2);
	        				}
            			}
        			
        				//城市三端总PV
        				sql.delete(0, sql.length()+1);
        				sql.append(" select '" + curDate + "','"+busiName+"', CONCAT(a.disp_city1_name,'_',a.disp_cate2_name),");
        				sql.append(" sum(a.pv)/count(distinct stat_date) as '总PV' ");
        				sql.append(" from (");
        				//PC
        				sql.append(" select stat_date,disp_city1_name,disp_cate2_name,sum(pv) as pv");
        				sql.append(" from t_netflow_pc_city_cate_pv_" + curmonthList.get(0).split(";")[0]);
        				sql.append(" where stat_date >= date_sub('"+curDate+"',interval 6 day)  ");
        				sql.append(" and stat_date <= '"+curDate+ "' ");
        				sql.append(" and disp_city1_name ='" + city + "'  and disp_cate2_name ='" + cate + "'");

        				if("ershouchuangxin".equals(busiName)){							
        					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
        				}else if("zhaoshangjiameng".equals(busiName)){							
        					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
        				}else if("zhaopin".equals(busiName))
        				{
    						sql.append(" and business_type ='" + busiName +"' and " + busiLineConsMap.get(busiName) );
        				}else{
        						sql.append(" and business_type ='" + busiName +"' ");
        				}
        				
        				sql.append(" and page_type='all' ");
        				sql.append(" group by stat_date");
        				
        				if(curmonthList.size() > 1){
        					sql.append(" union all ");
        					sql.append(" select stat_date,disp_city1_name,disp_cate2_name,sum(pv) as pv");
        					sql.append(" from t_netflow_pc_city_cate_pv_" + curmonthList.get(1).split(";")[0]);
        					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
        					sql.append(" and stat_date <= '"+searchDate+ "' ");
        					sql.append(" and disp_city1_name ='" + city + "'  and disp_cate2_name ='" + cate + "'");

        					if("ershouchuangxin".equals(busiName)){							
            					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
            				}else if("zhaoshangjiameng".equals(busiName)){							
            					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
            				}else if("zhaopin".equals(busiName))
            				{
        						sql.append(" and business_type ='" + busiName +"' and " + busiLineConsMap.get(busiName) );
            				}else{
            						sql.append(" and business_type ='" + busiName +"' ");
            				}
        					
        					sql.append(" and page_type='all' ");
            				sql.append(" group by stat_date");
        				}
        				//M
        				sql.append(" union all ");
        				sql.append(" select stat_date,disp_city1_name,disp_cate2_name,sum(pv) as pv ");
        				sql.append(" from t_netflow_m_city_cate_pv_" + curmonthList.get(0).split(";")[0]);
        				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
        				sql.append(" and stat_date <= '"+searchDate+ "' ");
        				sql.append(" and disp_city1_name ='" + city + "'  and disp_cate2_name ='" + cate + "'");
        				
        				if("ershouchuangxin".equals(busiName)){							
        					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
        				}else if("zhaoshangjiameng".equals(busiName)){							
        					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
        				}else if("zhaopin".equals(busiName))
        				{
    						sql.append(" and business_type ='" + busiName +"' and " + busiLineConsMap.get(busiName) );
        				}else{
        						sql.append(" and business_type ='" + busiName +"' ");
        				}
        				
        				sql.append(" and page_type='all' ");
        				sql.append(" group by stat_date");
        				
        				if(curmonthList.size() > 1){
        					sql.append(" union all ");
        					sql.append(" select stat_date,disp_city1_name,disp_cate2_name,sum(pv) as pv ");
        					sql.append(" from t_netflow_m_city_cate_pv_" + curmonthList.get(1).split(";")[0]);
        					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
        					sql.append(" and stat_date <= '"+searchDate+ "' ");
        					sql.append(" and disp_city1_name ='" + city + "'  and disp_cate2_name ='" + cate + "'");
        					
        					if("ershouchuangxin".equals(busiName)){							
            					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
            				}else if("zhaoshangjiameng".equals(busiName)){							
            					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
            				}else if("zhaopin".equals(busiName))
            				{
        						sql.append(" and business_type ='" + busiName +"' and " + busiLineConsMap.get(busiName) );
            				}else{
            						sql.append(" and business_type ='" + busiName +"' ");
            				}
        					
        					sql.append(" and page_type='all' ");
            				sql.append(" group by stat_date");
        				}
        				//APP
        				sql.append(" union all ");
        				sql.append(" select stat_date,disp_city1_name,disp_cate2_name,sum(pv) as pv ");
        				sql.append(" from t_netflow_app_pv_" + curmonthList.get(0).split(";")[0]);
        				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
        				sql.append(" and stat_date <= '"+searchDate+ "' ");
        				sql.append(" and disp_city1_name ='" + city + "'  and disp_cate2_name ='" + cate + "'");
        				
        				if("ershouchuangxin".equals(busiName)){							
        					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
        				}else if("zhaoshangjiameng".equals(busiName)){							
        					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
        				}else if("zhaopin".equals(busiName))
        				{
    						sql.append(" and business_type ='" + busiName +"' and " + busiLineConsMap.get(busiName) );
        				}else{
        						sql.append(" and business_type ='" + busiName +"' ");
        				}
        				
        				sql.append(" and page_type='all' ");
        				sql.append(" and disp_cate3_name='all' and disp_city3_name='all' ");
        				sql.append(" group by stat_date");
        				
        				if(curmonthList.size() > 1){
        					sql.append(" union all ");
        					sql.append(" select stat_date,disp_city1_name,disp_cate2_name,sum(pv) as pv ");
        					sql.append(" from t_netflow_app_pv_" + curmonthList.get(1).split(";")[0]);
        					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
        					sql.append(" and stat_date <= '"+searchDate+ "' ");
        					sql.append(" and disp_city1_name ='" + city + "'  and disp_cate2_name ='" + cate + "'");
        					
        					if("ershouchuangxin".equals(busiName)){							
            					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
            				}else if("zhaoshangjiameng".equals(busiName)){							
            					sql.append(" and " + busiLineConsMap.get(busiName) +"and business_type ='all'");
            				}else if("zhaopin".equals(busiName))
            				{
        						sql.append(" and business_type ='" + busiName +"' and " + busiLineConsMap.get(busiName) );
            				}else{
            						sql.append(" and business_type ='" + busiName +"' ");
            				}
        					
        					sql.append(" and page_type='all' ");
        					sql.append(" and disp_cate3_name='all' and disp_city3_name='all' ");
            				sql.append(" group by stat_date");
        				}
        				sql.append(" )a ");
        				
        				sql.append(" :"+ DBType.db_94_data_app.getValue());
        				List<String> cols3 = new ArrayList<String>();
        				cols3.add("statDate");
        				cols3.add("busiLine");
        				cols3.add("type");
        				cols3.add("allPv");
        				sqlMap2.put(sql.toString(), cols3);

        			}
 				
        		}
			}
						
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("type");
		
		List<BWRLineCityCate> rsList = comDeal.AssembledDataBySqlMap(sqlMap2, BWRLineCityCate.class, gbList);
		
		//把Top20Map里本周的数放入rsList
		for(BWRLineCityCate rsvo:rsList)
		{
			if(rsvo.getStatDate().equals(searchDate)) 
			{
				List<BWRLineCityCate> list=Top20Map.get(rsvo.getBusiLine());
				
				for(BWRLineCityCate vo:list)
				{
					if(rsvo.getType().equals(vo.getType()))
					{
						rsvo.setAllCash(vo.getAllCash());
						rsvo.setJzCash(vo.getJzCash());
						rsvo.setZdCash(vo.getZdCash());
						rsvo.setZnCash(vo.getZnCash());
						rsvo.setJzCusCount(vo.getJzCusCount());
						rsvo.setZdCusCount(vo.getZdCusCount());
						rsvo.setZnCusCount(vo.getZnCusCount());
						
						break;
					}
					
					
				}
			}				
		}		
		
		
		Map<String,List<BWRLineCityCate>>  rsMap = new HashMap<String, List<BWRLineCityCate>>();
		for (BWRLineCityCate vo : rsList) {
			
			String busiLine = vo.getBusiLine();
			busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			List<BWRLineCityCate> tempList = new ArrayList<BWRLineCityCate>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
			
			//计算 流量变现率 和 客户充足度     
			//流量变现率 =  精准置顶智能现金SUM/城市总PV
			//客户充足度 =  精准置顶智能客户数SUM/城市总PV
			if(vo.getAllPv()==0)
			{
				vo.setFluxTranRate(0);
				vo.setCusAdequacy(0);
			}
			else
			{
				vo.setFluxTranRate((double)(vo.getJzCash() + vo.getZdCash() + vo.getZnCash()) / vo.getAllPv()) ;
				vo.setCusAdequacy((double)(vo.getJzCusCount() + vo.getZdCusCount() + vo.getZnCusCount()) / vo.getAllPv()) ;
			}
		
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}
		return rsMap;
	
	}
	

	@Override
	public Map<String,List<BWRNoteEffectData>>  getBWRNoteEffectList(List<String> dateList)
			throws Exception {
		
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();

		HashMap<String,String> CusTypeMap = new HashMap<String,String>();
		CusTypeMap.put("all", " ");
		CusTypeMap.put("vip", " and is_vip=1 ");
		CusTypeMap.put("novip", " and (is_vip=0 or is_vip is null ) ");

		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);
		
			for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
					String busiName = it.next();
				
				if("all".equals(busiName) || "ershouche".equals(busiName) || "fangchan".equals(busiName) ){ //总体 二手车 房产不看
					continue;
				}
				
				
				for(String CusType : CusTypeMap.keySet()){
					//精准的部分
					sql.delete(0, sql.length()+1);
					
					sql.append(" select '" + searchDate + "','"+busiName+"','" + CusType + "',");
					sql.append(" round(sum(cost)/100/count(distinct stat_date),3)  as'精准消费', ");
					sql.append(" round(sum(kh)/count(distinct stat_date),0)as'精准客户数' ,");
					sql.append(" round(sum(cpc_info_num)/sum(kh),2)as '户均条数',");
					sql.append(" round(sum(cost)/100/sum(click),2)as'ACP',");
					sql.append(" round(sum(click)/sum(cpc_info_num),2)as '帖均效果'");
					sql.append(" from( ");
					sql.append(" select stat_date,sum(amount) as cost,sum(click)click, ");
					sql.append(" count(distinct user_id)as kh,count(distinct creative_id)as cpc_info_num  ");
					sql.append(" from t_pre_cpc_success_detail_stat_" + monthList.get(0).split(";")[0]);
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					if(!"all".equals(busiName)){
						if("zhaoshangjiameng".equals(busiName)){							
							sql.append(" and disp_cate1 in ('招商加盟') ");
						}else{
							sql.append(" and business_type ='" + busiName +"' ");
						}
					}
					sql.append("and amount>0 ");
					sql.append(CusTypeMap.get(CusType));
					
					sql.append(" group by stat_date ");
					//跨月处理
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select stat_date,sum(amount) as cost,sum(click)click, ");
						sql.append(" count(distinct user_id)as kh,count(distinct creative_id)as cpc_info_num  ");
						sql.append(" from t_pre_cpc_success_detail_stat_" + monthList.get(1).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");
						
						if(!"all".equals(busiName)){
							if("zhaoshangjiameng".equals(busiName)){							
								sql.append(" and disp_cate1 in ('招商加盟') ");
							}else{
								sql.append(" and business_type ='" + busiName +"' ");
							}
						}
						sql.append("and amount>0 ");
						sql.append(CusTypeMap.get(CusType));
						
						sql.append(" group by stat_date ");
					}
					sql.append(" )a ");
					
					sql.append(" :"+ DBType.db_133_dm_sales.getValue());
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("busiLine");
					cols1.add("CusType");
	
					cols1.add("jzCost");
					cols1.add("jzCusCount");
					cols1.add("jzCusAvgInfo");
					cols1.add("ACP");
					cols1.add("jzInfoAvgEffect");
			
					sqlMap.put(sql.toString(), cols1);
					
					
					//置顶的部分
					sql.delete(0, sql.length()+1);
					
					sql.append(" select '" + searchDate + "','"+busiName+"','" + CusType + "',");
					sql.append(" round(sum(cost)/100/count(distinct stat_date),3)as '置顶总消费', ");
					sql.append(" round(sum(kh)/count(distinct stat_date),0)as '置顶客户数',");
					sql.append(" round(sum(top_info_num)/sum(kh),2)as'户均条数',");
					sql.append(" round(sum(top_hour)/sum(top_info_num),2)as'单条时长',");
					sql.append(" round(sum(cost)/100/sum(top_hour),2)as'小时单价',");
					sql.append(" round(sum(click)/sum(top_info_num),2)as'帖均效果'");
					sql.append(" from( ");
					sql.append(" select stat_date,sum(cost) as 'cost',count(distinct user_id) as 'kh', ");
					sql.append(" sum(click)as 'click',count(distinct info_id) as 'top_info_num',sum(top_hour) as 'top_hour' ");
						
						sql.append(" from( ");
						sql.append(" select stat_date,info_id,user_id,sum(cost)as cost,sum(click)as click,max(top_hour)as top_hour ");
						sql.append(" from t_zhiding_info_detail_new_" + monthList.get(0).split(";")[0]);
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");
						if(!"all".equals(busiName)){
							if("ershouchuangxin".equals(busiName)){							
								sql.append(" and " + busiLineConsMap.get(busiName) );
							}else if("zhaoshangjiameng".equals(busiName)){							
								sql.append(" and " + busiLineConsMap.get(busiName) );
							}else{
								sql.append(" and business_type_biz ='" + busiName +"' ");
							}
						}
						sql.append(CusTypeMap.get(CusType));
						sql.append(" group by stat_date,info_id ");
						//跨月处理
						if(monthList.size() > 1){
							sql.append(" UNION all  ");
							sql.append(" select stat_date,info_id,user_id,sum(cost)as cost,sum(click)as click,max(top_hour)as top_hour ");
							sql.append(" from t_zhiding_info_detail_new_" + monthList.get(1).split(";")[0]);
							sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
							sql.append(" and stat_date <= '"+searchDate+ "' ");
							if(!"all".equals(busiName)){
								if("ershouchuangxin".equals(busiName)){							
									sql.append(" and " + busiLineConsMap.get(busiName) );
								}else if("zhaoshangjiameng".equals(busiName)){							
									sql.append(" and " + busiLineConsMap.get(busiName) );
								}else{
									sql.append(" and business_type_biz ='" + busiName +"' ");
								}
							}
							sql.append(CusTypeMap.get(CusType));
							sql.append(" group by stat_date,info_id ");
						}
						sql.append(" )a ");
					
					sql.append("where cost>0 ");
					sql.append(" group by stat_date ");	
					sql.append(" )a ");	

					sql.append(" :"+ DBType.db_94_data_app.getValue());
					List<String> cols2 = new ArrayList<String>();
					cols2.add("statDate");
					cols2.add("busiLine");
					cols2.add("CusType");
	
					cols2.add("zdCost");
					cols2.add("zdCusCount");
					cols2.add("zdCusAvgInfo");
					cols2.add("zdTime");
					cols2.add("zdHourPrice");
					cols2.add("zdInfoAvgEffect");

					sqlMap.put(sql.toString(), cols2);
					
				}
			}
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("CusType");
		
		List<BWRNoteEffectData> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRNoteEffectData.class, gbList);
		
		Map<String,List<BWRNoteEffectData>>  rsMap = new HashMap<String, List<BWRNoteEffectData>>();
		for (BWRNoteEffectData vo : rsList) {
			
			String busiLine = MyStore.busilineTran(vo.getBusiLine());		
			vo.setBusiLine(busiLine);
			
			String type = vo.getCusType();
			if(type.equals("all"))
			{
				vo.setCusType("总体");
			}
			else if(type.equals("vip"))
			{
				vo.setCusType("会员");
			}else			
			{
				vo.setCusType("非会员");
			}
			
			List<BWRNoteEffectData> tempList = new ArrayList<BWRNoteEffectData>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
			
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}
		
		return rsMap;
	}

	
	@Override
	public Map<String,List<BWRBalanceCusLevel>>  getBWRBalanceCusLevelMap(List<String> dateList)
		throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
	
		HashMap<String,String> conditionMap = new HashMap<String,String>();
		conditionMap.put("ershouchuangxin", "7000");
		conditionMap.put("zhaopin", "8000");
		conditionMap.put("huangye", "10000");
		conditionMap.put("zhaoshangjiameng", "10000");

		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);			
				
			for(Iterator<String> it = conditionMap.keySet().iterator(); it.hasNext();){
				String busiName = it.next();

				if("all".equals(busiName) || "ershouche".equals(busiName) || "fangchan".equals(busiName) ){ //总体 二手车 房产不看
					continue;
				}
				
				sql.delete(0, sql.length()+1);
					
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" (case when (a.is_vip>0 and a.adcash_consume>="+conditionMap.get(busiName)+") then '会员大客户'");
				sql.append(" when (a.is_vip>0 and a.adcash_consume>0)then '会员小客户'");
				sql.append(" when (a.is_vip>0) then '会员非活跃'");
				sql.append(" when (a.is_vip=0 and a.adcash_consume>0) then '非会员活跃'");
				sql.append(" when (a.is_vip=0) then '非会员非活跃'  end) as '客户类型', ");
				
				sql.append(" round(sum(a.total_balance)/100/10000,5) AS '总余额(W)', ");
				sql.append(" round(sum(a.adcash_balance)/100/10000,5) AS '现金余额(W)',");
				sql.append(" round(sum(a.adcash_consume)/100/10000,5) as '现金消耗(W)',");
				sql.append(" count(a.user_id) as '客户数',");
				sql.append(" count(case when a.total_balance>0 then a.user_id end) as '有余额客户数',");	
				sql.append(" round(sum(a.frequency),2)as '总消耗频次' ");	
				
				sql.append(" from( ");
					sql.append(" select user_id, ");
					sql.append(" sum(case when (is_vip is null or is_vip =0) then '0' else '1' end)as is_vip,  ");
					sql.append(" sum(adcash_consume)/count(distinct stat_date) as 'adcash_consume',  ");				
					sql.append(" count(distinct case when adcash_consume>0 then stat_date end) as 'frequency',  ");
					sql.append(" sum(case when stat_date='"+searchDate+"' then (adcash_balance+adgift_balance) end) as 'total_balance',  ");
					sql.append(" sum(case when stat_date='"+searchDate+"' then adcash_balance end) as 'adcash_balance'  ");
					
					sql.append(" from( ");
					
					sql.append(" select user_id,is_vip,adcash_consume,stat_date,adcash_balance,adgift_balance ");
					sql.append(" from t_common_user_account_new_" + monthList.get(0).split(";")[0]);	
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
							
					sql.append(" and " + busiLineConsMap.get(busiName).replace("disp_cate1_name", "source_cate1_name").replace("disp_cate2_name", "source_cate2_name") );
					
					//跨月处理
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select user_id,is_vip,adcash_consume,stat_date,adcash_balance,adgift_balance ");
						sql.append(" from t_common_user_account_new_" + monthList.get(1).split(";")[0]);	
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");
						
						sql.append(" and " + busiLineConsMap.get(busiName).replace("disp_cate1_name", "source_cate1_name").replace("disp_cate2_name", "source_cate2_name") );
						
					}
					sql.append(" )b ");
					sql.append(" group by user_id ");
					
					sql.append(" )a ");
					sql.append(" group by 3 ");

					sql.append(" :"+ DBType.db_94_data_app.getValue());
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("busiLine");
					cols1.add("CusType");
	
					cols1.add("balance");
					cols1.add("cashBalance");
					cols1.add("cost");
					cols1.add("Cus");
					cols1.add("balanceCus");					
					cols1.add("frequency");

					sqlMap.put(sql.toString(), cols1);	
			}
			
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("CusType");
		
		List<BWRBalanceCusLevel> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRBalanceCusLevel.class, gbList);
		
		//计算总体、会员、会员活跃、非会员		
		for(String busiName :conditionMap.keySet())
		{	
//			if(!"zhaoshangjiameng".equals(busiName)){ 
//				continue;
//			}
			
			for(String date : dateList)
			{
				BWRBalanceCusLevel allVO =new BWRBalanceCusLevel();
				BWRBalanceCusLevel vipVO =new BWRBalanceCusLevel();
				BWRBalanceCusLevel vip_activeVO =new BWRBalanceCusLevel();
				BWRBalanceCusLevel unvipVO =new BWRBalanceCusLevel();
				
				allVO.setBusiLine(busiName);
				allVO.setStatDate(date);
				allVO.setCusType("总体");
				
				vipVO.setBusiLine(busiName);
				vipVO.setStatDate(date);
				vipVO.setCusType("会员");
				
				vip_activeVO.setBusiLine(busiName);
				vip_activeVO.setStatDate(date);
				vip_activeVO.setCusType("会员活跃");
				
				unvipVO.setBusiLine(busiName);
				unvipVO.setStatDate(date);
				unvipVO.setCusType("非会员");
				
				
				for (BWRBalanceCusLevel vo : rsList) {
					
					if(busiName.equals(vo.getBusiLine()) && date.equals(vo.getStatDate()))					
					{

						allVO.setBalance(allVO.getBalance()+vo.getBalance());
						allVO.setCashBalance(allVO.getCashBalance()+vo.getCashBalance());
						allVO.setCost(allVO.getCost()+vo.getCost());
						allVO.setCus(allVO.getCus()+vo.getCus());
						allVO.setBalanceCus(allVO.getBalanceCus()+vo.getBalanceCus());
						allVO.setFrequency(allVO.getFrequency()+vo.getFrequency());
						
						if(vo.getCusType().equals("会员大客户") || vo.getCusType().equals("会员小客户"))
						{
							vipVO.setBalance(vipVO.getBalance()+vo.getBalance());
							vipVO.setCashBalance(vipVO.getCashBalance()+vo.getCashBalance());
							vipVO.setCost(vipVO.getCost()+vo.getCost());
							vipVO.setCus(vipVO.getCus()+vo.getCus());
							vipVO.setBalanceCus(vipVO.getBalanceCus()+vo.getBalanceCus());
							vipVO.setFrequency(vipVO.getFrequency()+vo.getFrequency());
							
							vip_activeVO.setBalance(vip_activeVO.getBalance()+vo.getBalance());
							vip_activeVO.setCashBalance(vip_activeVO.getCashBalance()+vo.getCashBalance());
							vip_activeVO.setCost(vip_activeVO.getCost()+vo.getCost());
							vip_activeVO.setCus(vip_activeVO.getCus()+vo.getCus());
							vip_activeVO.setBalanceCus(vip_activeVO.getBalanceCus()+vo.getBalanceCus());
							vip_activeVO.setFrequency(vip_activeVO.getFrequency()+vo.getFrequency());
						}
						else if(vo.getCusType().equals("会员非活跃") )
						{
							vipVO.setBalance(vipVO.getBalance()+vo.getBalance());
							vipVO.setCashBalance(vipVO.getCashBalance()+vo.getCashBalance());
							vipVO.setCost(vipVO.getCost()+vo.getCost());
							vipVO.setCus(vipVO.getCus()+vo.getCus());
							vipVO.setBalanceCus(vipVO.getBalanceCus()+vo.getBalanceCus());
							vipVO.setFrequency(vipVO.getFrequency()+vo.getFrequency());
						}
						else if(vo.getCusType().equals("非会员活跃") || vo.getCusType().equals("非会员非活跃"))
						{
							unvipVO.setBalance(unvipVO.getBalance()+vo.getBalance());
							unvipVO.setCashBalance(unvipVO.getCashBalance()+vo.getCashBalance());
							unvipVO.setCost(unvipVO.getCost()+vo.getCost());
							unvipVO.setCus(unvipVO.getCus()+vo.getCus());
							unvipVO.setBalanceCus(unvipVO.getBalanceCus()+vo.getBalanceCus());
							unvipVO.setFrequency(unvipVO.getFrequency()+vo.getFrequency());
						}
					}	
				}			
				rsList.add(allVO);
				rsList.add(vipVO);
				rsList.add(vip_activeVO);
				rsList.add(unvipVO);
			}
		}
		
		//计算ARPU(现金消耗/客户数) 消耗频次(总频次/user_id ) 消耗周期(现金余额/现金消耗 )
		
		for (BWRBalanceCusLevel vo : rsList) {
			if(vo.getCus()!=0)
			{
				vo.setARPU(vo.getCost()*10000 / vo.getCus());
				vo.setFrequency_userid(vo.getFrequency() / vo.getCus());
			}
			if(vo.getCost()!=0)
			{
				vo.setCostCyc(vo.getCashBalance() / vo.getCost());
			}

		}

		Map<String,List<BWRBalanceCusLevel>>  rsMap = new HashMap<String, List<BWRBalanceCusLevel>>();
		for (BWRBalanceCusLevel vo : rsList) {
			
			String busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			List<BWRBalanceCusLevel> tempList = new ArrayList<BWRBalanceCusLevel>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
			
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}
		
		return rsMap;
	}
}
